> 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)
signature.asc
Description: This is a digitally signed message part