Re: AUTO_INCREMENT patch - Mailing list pgsql-hackers

From Rod Taylor
Subject Re: AUTO_INCREMENT patch
Date
Msg-id 1059872184.43336.191.camel@jester
Whole thread Raw
In response to Re: AUTO_INCREMENT patch  (des@des.no (Dag-Erling Smørgrav))
List pgsql-hackers
> des=# create table test ( id serial, word text );
> NOTICE:  CREATE TABLE will create implicit sequence "test_id_seq" for SERIAL column "test.id"
> CREATE TABLE
> des=# create rule test_id_generate as
> des-# on insert to test do instead
> des-# insert into test ( id, word ) values ( default, new.word );
> CREATE RULE
> des=# insert into test ( id, word ) values ( 42, 'hello' );
> ERROR:  infinite recursion detected in rules for relation "test"
> des=# insert into test ( word ) values ( 'hello' );
> ERROR:  infinite recursion detected in rules for relation "test"

Shoot, sorry, I forgot you will want to do this on a view. In my case I
have actually used 2 different schemas.  The JBoss user(s) have their
default search_path setup with jboss, data.  Updates and deletes are
equally fun.

You may find you prefer a trigger for this instead -- it won't complain
about recursion.

t=# begin;
BEGIN
t=# create schema data
t-#   create table test (id serial, word text);
NOTICE:  CREATE TABLE will create implicit sequence "test_id_seq" for
SERIAL column "test.id"
CREATE SCHEMA
t=#
t=# create schema jboss
t-#   create view test as select * from data.test;
CREATE SCHEMA
t=#
t=# create rule test_id_generate as on insert to jboss.test
t-#   do instead
t-#   insert into data.test (id, word) values (default, new.word);
CREATE RULE
t=#
t=# insert into jboss.test (word) values ('hello');
INSERT 17347 1
t=#
t=# insert into jboss.test (id, word) values (null, 'hello');
INSERT 17348 1
t=#
t=# insert into jboss.test (id, word) values ('22', 'hello');
INSERT 17349 1
t=#
t=#
t=# select * from jboss.test;id | word
----+------- 1 | hello 2 | hello 3 | hello
(3 rows)
t=# select * from data.test;id | word
----+------- 1 | hello 2 | hello 3 | hello
(3 rows)


pgsql-hackers by date:

Previous
From: des@des.no (Dag-Erling Smørgrav)
Date:
Subject: Re: AUTO_INCREMENT patch
Next
From: "Andrew Dunstan"
Date:
Subject: Re: AUTO_INCREMENT patch