From: | "Mikael Carneholm" <Mikael(dot)Carneholm(at)WirelessCar(dot)com> |
---|---|
To: | <pgsql-bugs(at)postgresql(dot)org> |
Subject: | Re: create OR REPLACE rule bug |
Date: | 2005-11-05 22:50:07 |
Message-ID: | 7F10D26ECFA1FB458B89C5B4B0D72C2B088200@sesrv12.wirelesscar.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
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(at)postgresql(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2005-11-05 22:51:38 | Re: create OR REPLACE rule bug |
Previous Message | Mikael Carneholm | 2005-11-05 22:05:17 | create OR REPLACE rule bug |