Re: Question about insert/update RULEs.

On Sun, Jan 09, 2005 at 06:45:54AM -0500, Dmitri Bichko wrote:

> CREATE TABLE "foo" (
> "foo_id" serial PRIMARY KEY,
> "type" varchar NOT NULL DEFAULT 'base' CONSTRAINT types CHECK
> (type IN ('base', 'bar'))
> "a" varchar NOT NULL,
> "b" varchar DEFAULT 'some text',
> );
>
> CREATE TABLE "foo_bar" (
> "foo_id" integer PRIMARY KEY CONSTRAINT foo REFERENCES foo (foo_id) ON
> DELETE CASCADE ON UPDATE CASCADE DEFERABLE,
> "c" varchar
> );
>
> CREATE VIEW "bar" AS
> SELECT f.foo_id, f.a, f.b, b.c
> FROM foo f JOIN foo_bar b USING(foo_id);
>
> CREATE RULE "bar_insert" AS ON INSERT TO "bar"
> DO INSTEAD (
> INSERT INTO "foo" (foo_id, type, a, b) VALUES
> (NEW.foo_id, 'bar', NEW.a, NEW.b);
> INSERT INTO "foo_bar" (foo_id, c) VALUES (NEW.foo_id,
> NEW.c);
> );
>
> The problem is that for the sequence to do the right thing, I have to
> select nextval first in a separate query and then pass it explicitely to
> INSERT INTO "bar" (which C:DBI does anyway, but I'd like to do better).
>
> If I were to do this:
> foo insert: foo_id = COALESCE(NEW.foo_id, nextval('foo_foo_id_seq')),
> foo_bar insert: foo_id = COALESCE(NEW.foo_id,
> currval('foo_foo_id_seq')),

I believe you may be trying to do something like the following:

CREATE RULE bar_insert AS
ON INSERT TO bar
DO INSTEAD
(
INSERT INTO
foo ( food_id, type, a, b )
VALUES
( nextval( 'foo_foo_id_seq' ), ... );

INSERT INTO
foo_bar ( foo_id, c )
VALUES
( currval( 'foo_foo_id_seq' ), ... );
);

(Which would mean there's no reason for view 'bar' to display foo_id)

Hmm, just noticed you defined foo.foo_id to be type 'serial', so you
could omit foo_id in the first INSERT.

> Will the currval() be guaranteed to be the same value that the nextval()
> got? I am not quite sure what the "scope" of currval() is.

The value of currval will be predictable within the current session.
I.E., if another session increments the sequence, the value returned by
currval in the current session won't change.

Best.

--
Ron Peterson
Network & Systems Manager
Mount Holyoke College
http://www.mtholyoke.edu/~rpeterso