Forget that, is seems as a EMS PostgreSQL Manager bug (no problem creating the rule when executed from the pgAdmin III Query tool)
However, the rule does not work as expected (but I have been warned, see http://archives.postgresql.org/pgsql-general/2005-10/msg01902.php) insert into parttest.mastertab (id, datecol) values (nextval('mastertab_id_seq'), '20050101'); insert into parttest.mastertab (id, datecol) values (nextval('mastertab_id_seq'), '20050201'); SELECT p.relname, m.id, m.datecol FROM mastertab m, pg_class p WHERE m.tableoid = p.oid ORDER BY id; relname|id|datecol ---------------------------- mastertab_jan05|1|2005-01-01 mastertab_feb05|2|2005-02-01 update mastertab set datecol = '20050228' where id = 1; SELECT p.relname, m.id, m.datecol FROM mastertab m, pg_class p WHERE m.tableoid = p.oid ORDER BY id; relname|id|datecol ---------------------------- mastertab_feb05|2|2005-02-01 (row with id 1 was deleted but not re-inserted) /Mikael -----Original Message----- From: Mikael Carneholm Sent: den 5 november 2005 23:05 To: 'pgsql-bugs@postgresql.org' Subject: create OR REPLACE rule bug I believe this is a bug: -- OR REPLACE present create or replace rule "mastertab_jan05_update" as on update to mastertab where (NEW.datecol < '20050101' or NEW.datecol >= '20050201') and (OLD.datecol >= '20050101' and OLD.datecol < '20050201') do instead ( -- multiple commands delete from mastertab_jan05 where id = OLD.id; insert into mastertab (id, datecol) values (OLD.id, NEW.datecol); ); => ERROR: syntax error at end of input at character 255 -- OR REPLACE not present create rule "mastertab_jan05_update" as on update to mastertab where (NEW.datecol < '20050101' or NEW.datecol >= '20050201') and (OLD.datecol >= '20050101' and OLD.datecol < '20050201') do instead ( -- multiple commands delete from mastertab_jan05 where id = OLD.id; insert into mastertab (id, datecol) values (OLD.id, NEW.datecol); ); => Query OK, 0 rows affected (0,00 sec) -- OR REPLACE present create or replace rule "mastertab_jan05_update" as on update to mastertab where (NEW.datecol < '20050101' or NEW.datecol >= '20050201') and (OLD.datecol >= '20050101' and OLD.datecol < '20050201') do instead ( -- single command, no semicolon delete from mastertab_jan05 where id = OLD.id ); => Query OK, 0 rows affected (0,00 sec) -- OR REPLACE present create or replace rule "mastertab_jan05_update" as on update to mastertab where (NEW.datecol < '20050101' or NEW.datecol >= '20050201') and (OLD.datecol >= '20050101' and OLD.datecol < '20050201') do instead ( -- single command, semicolon present delete from mastertab_jan05 where id = OLD.id; ); => ERROR: syntax error at end of input at character 255 Ie, the "OR REPLACE" token is broken in that is doesn't work with multiple commands (semicolons?) /Mikael ---------------------------(end of broadcast)--------------------------- TIP 4: Have you searched our list archives? http://archives.postgresql.org
