--
-- PostgreSQL database dump
--

SET client_encoding = 'WIN';
SET check_function_bodies = false;

SET SESSION AUTHORIZATION 'root';

--
-- Name: public; Type: SCHEMA; Schema: -; Owner: root
--

--
-- Name: SCHEMA public; Type: COMMENT; Schema: -; Owner: root
--

COMMENT ON SCHEMA public IS 'Standard public schema';


SET search_path = public, pg_catalog;

--
-- Name: plpgsql_call_handler(); Type: FUNCTION; Schema: public; Owner: root
--

CREATE FUNCTION plpgsql_call_handler() RETURNS language_handler
    AS '$libdir/plpgsql', 'plpgsql_call_handler'
    LANGUAGE c;


--
-- Name: plpgsql_validator(oid); Type: FUNCTION; Schema: public; Owner: root
--

CREATE FUNCTION plpgsql_validator(oid) RETURNS void
    AS '$libdir/plpgsql', 'plpgsql_validator'
    LANGUAGE c;


SET SESSION AUTHORIZATION DEFAULT;

--
-- Name: plpgsql; Type: PROCEDURAL LANGUAGE; Schema: public; Owner: 
--

CREATE TRUSTED PROCEDURAL LANGUAGE plpgsql HANDLER plpgsql_call_handler VALIDATOR plpgsql_validator;


SET SESSION AUTHORIZATION 'root';

--
-- Name: fk_violation_bug(); Type: FUNCTION; Schema: public; Owner: root
--

CREATE FUNCTION fk_violation_bug() RETURNS integer
    AS '

begin

  insert into detailed (main_id, task)

    values (-1, ''bug'');

  return 0;

end;

'
    LANGUAGE plpgsql;


--
-- Name: fk_violation_bug2(); Type: FUNCTION; Schema: public; Owner: root
--

CREATE FUNCTION fk_violation_bug2() RETURNS integer
    AS '

begin

  insert into detailed (main_id, task)

    values (-1, ''bug'');

  return 0;

  exception

    when foreign_key_violation then

      raise warning ''foreign key violation'';

      return -1;

    when others then

      raise warning ''other error occured'';

      return -2;

end;

'
    LANGUAGE plpgsql;


--
-- Name: fk_violation_bug3(); Type: FUNCTION; Schema: public; Owner: root
--

CREATE FUNCTION fk_violation_bug3() RETURNS integer
    AS '

declare

  tmp int4;

begin

  insert into detailed (main_id, task)

    values (-1, ''bug'');

  raise notice ''should not be there if the foreign key violation occured'';

  raise notice ''local variable FOUND value: %'', FOUND;

  GET DIAGNOSTICS tmp = ROW_COUNT;

  raise notice ''DIAGNOSTICS ROW_COUNT value: %'', tmp;

  tmp := 1/0;

  raise notice ''should not be there if division by zero occured'';

  return 0;

  exception

    when foreign_key_violation then

      raise warning ''foreign key violation'';

      return -1;

    when division_by_zero then

      raise warning ''division by zero'';

      return -2;

    when others then

      raise warning ''other error occured'';

      return -3;

end;

'
    LANGUAGE plpgsql;


SET default_with_oids = true;

--
-- Name: detailed; Type: TABLE; Schema: public; Owner: root
--

CREATE TABLE detailed (
    main_id integer,
    task text NOT NULL
);


--
-- Name: main; Type: TABLE; Schema: public; Owner: root
--

CREATE TABLE main (
    id serial NOT NULL,
    name character varying(64)
);


--
-- Name: main_id_seq; Type: SEQUENCE SET; Schema: public; Owner: root
--

SELECT pg_catalog.setval(pg_catalog.pg_get_serial_sequence('main', 'id'), 1, false);


--
-- Data for Name: detailed; Type: TABLE DATA; Schema: public; Owner: root
--

INSERT INTO detailed (main_id, task) VALUES (1, 'task11');
INSERT INTO detailed (main_id, task) VALUES (1, 'task12');


--
-- Data for Name: main; Type: TABLE DATA; Schema: public; Owner: root
--

INSERT INTO main (id, name) VALUES (1, 'name1');
INSERT INTO main (id, name) VALUES (2, 'name2');
INSERT INTO main (id, name) VALUES (3, 'name3');


--
-- Name: main_pkey; Type: CONSTRAINT; Schema: public; Owner: root
--

ALTER TABLE ONLY main
    ADD CONSTRAINT main_pkey PRIMARY KEY (id);


--
-- Name: FK_main_detailed; Type: FK CONSTRAINT; Schema: public; Owner: root
--

ALTER TABLE ONLY detailed
    ADD CONSTRAINT "FK_main_detailed" FOREIGN KEY (main_id) REFERENCES main(id) ON UPDATE RESTRICT ON DELETE CASCADE;


SET SESSION AUTHORIZATION DEFAULT;

SET SESSION AUTHORIZATION 'root';

--
-- PostgreSQL database dump complete
--