From: Ron Peterson <rpeterso@mtholyoke.edu>
Date: Mon, 10 Jan 2005 21:14:56 -0500
To: Dmitri Bichko <dbichko@genpathpharma.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Question about insert/update RULEs.
Message-ID: <20050111021456.GA25826@mtholyoke.edu>
Mail-Followup-To: Dmitri Bichko <dbichko@genpathpharma.com>,
	pgsql-sql@postgresql.org
References: 
 <7A4ADADFC8AFF0478D47F63BEDD57CE30D0A00@gpmail.gphq.genpathpharma.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: 
 <7A4ADADFC8AFF0478D47F63BEDD57CE30D0A00@gpmail.gphq.genpathpharma.com>
Organization: Mount Holyoke College
User-Agent: Mutt/1.5.6+20040907i

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