From: Ron Peterson <rpeterso@mtholyoke.edu>
To: Dmitri Bichko <dbichko@genpathpharma.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Question about insert/update RULEs.
Date: Mon, 10 Jan 2005 21:14:56 -0500
Message-ID: <20050111021456.GA25826@mtholyoke.edu> (raw)
In-Reply-To: <7A4ADADFC8AFF0478D47F63BEDD57CE30D0A00@gpmail.gphq.genpathpharma.com>
References: <7A4ADADFC8AFF0478D47F63BEDD57CE30D0A00@gpmail.gphq.genpathpharma.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: rpeterso@mtholyoke.edu, dbichko@genpathpharma.com
Subject: Re: Question about insert/update RULEs.
In-Reply-To: <20050111021456.GA25826@mtholyoke.edu>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox