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

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

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 (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 >=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

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

"Mikael Carneholm" <Mikael.Carneholm (AT) WirelessCar (DOT) com> writes:
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

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

"Mikael Carneholm" <Mikael.Carneholm (AT) WirelessCar (DOT) com> writes:
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

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match