Re: plpgsql constraint checked data fails to restore

On Thu, Jun 23, 2005 at 11:47:37AM -0400, Jan Wieck wrote:
> On 6/20/2005 1:23 PM, Lee Harr wrote:
> >
> >You ask some great questions. Thanks.
>
> But not the really important one :-)

Maybe that's because it didn't need asking :-)

> The question I have is how exactly you manage to get the trigger fired
> when restoring the dump. By default, the dump created by pg_dump will
> create the table, fill in the data and create the trigger(s) only after
> that.

Not true for CHECK constraints -- pg_dump creates them with the
CREATE TABLE statement:

CREATE TABLE foo (
    id  integer PRIMARY KEY
);

CREATE TABLE bar (
    fooid  integer NOT NULL REFERENCES foo,
    x      integer CHECK (x > 0)
);

INSERT INTO foo (id) VALUES (1);
INSERT INTO foo (id) VALUES (2);

INSERT INTO bar (fooid, x) VALUES (1, 2);
INSERT INTO bar (fooid, x) VALUES (2, 3);

pg_dump testdb

[...]

CREATE TABLE bar (
    fooid integer NOT NULL,
    x integer,
    CONSTRAINT bar_x_check CHECK ((x > 0))
);

[...]

CREATE TABLE foo (
    id integer NOT NULL
);

[...]

COPY bar (fooid, x) FROM stdin;
1       2
2       3
\.

[...]

COPY foo (id) FROM stdin;
1
2
\.

[...]

ALTER TABLE ONLY foo
    ADD CONSTRAINT foo_pkey PRIMARY KEY (id);

[...]

ALTER TABLE ONLY bar
    ADD CONSTRAINT bar_fooid_fkey FOREIGN KEY (fooid) REFERENCES foo(id);

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/