Re: create OR REPLACE rule bug - Mailing list 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 ht=
tp://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 =3D p.oid
ORDER BY id;

relname|id|datecol
----------------------------
mastertab_jan05|1|2005-01-01
mastertab_feb05|2|2005-02-01

update mastertab set datecol =3D '20050228' where id =3D 1;

SELECT p.relname, m.id, m.datecol
FROM mastertab m, pg_class p
WHERE m.tableoid =3D 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=20
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 >=3D '20050201')
   and
   (OLD.datecol >=3D '20050101' and OLD.datecol < '20050201')
do instead (
   -- multiple commands
   delete from mastertab_jan05 where id =3D OLD.id;
   insert into mastertab (id, datecol) values (OLD.id, NEW.datecol);
);

=3D> 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 >=3D '20050201')
   and
   (OLD.datecol >=3D '20050101' and OLD.datecol < '20050201')
do instead (
   -- multiple commands
   delete from mastertab_jan05 where id =3D OLD.id;
   insert into mastertab (id, datecol) values (OLD.id, NEW.datecol);
);

=3D> 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 >=3D '20050201')
   and
   (OLD.datecol >=3D '20050101' and OLD.datecol < '20050201')
do instead (
   -- single command, no semicolon
   delete from mastertab_jan05 where id =3D OLD.id=20
);

=3D> 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 >=3D '20050201')
   and
   (OLD.datecol >=3D '20050101' and OLD.datecol < '20050201')
do instead (
   -- single command, semicolon present
   delete from mastertab_jan05 where id =3D OLD.id;
);

=3D> 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