create OR REPLACE rule bug

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

"Mikael Carneholm" <Mikael.Carneholm@WirelessCar.com> writes:

Ie, the "OR REPLACE" token is broken in that is doesn't work with
multiple commands (semicolons?)

None of these examples fail for me, in any PG version back to 7.3.
I speculate that the problem is in whatever client-side software
you are using (which you didn't say, nor did you mention which PG
version this is; tut tut).

regards, tom lane

"Mikael Carneholm" <Mikael.Carneholm@WirelessCar.com> writes:

However, the rule does not work as expected (but I have been warned, see http://archives.postgresql.org/pgsql-general/2005-10/msg01902.php)

Once you do the DELETE, there is no OLD row anymore, so there is nothing
for the INSERT to do.

You might have better luck implementing this stuff as triggers.

regards, tom lane