> 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)
