Hey,
I am trying to figure out some unexpected behavior in Postgresql.
When I create a rule that fires on a table after an update, and if
that rule has a SELECT statement in it, it seems to be attempting to
fire (on an empty set) regardless of how the conditional evaluates
after an update.
The result being that if I run an update on a table with such a rule,
instead of getting a message along the lines of "UPDATE (# of rows)" I
get the column names of the select statement with no rows and the
message "row number -1 is out of range 0..-1".
So first off, is having a select statement (I'm actually trying to run
a function) inside a rule that fires on an update considered bad
practice? I could do this through a trigger, but a rule just seems
more natural.
Here is some sql to setup an example of what I'm talking about:
CREATE TABLE test_table
(
id varchar(36) NOT NULL,
amount float8,
CONSTRAINT test_table_pkey PRIMARY KEY (id)
)
WITHOUT OIDS;
ALTER TABLE test_table OWNER TO postgres;
CREATE OR REPLACE RULE protect_id AS
ON UPDATE TO test_table
WHERE new.id::text <> old.id::text DO INSTEAD SELECT 'abc' AS test_select;
INSERT INTO test_table (id, amount) values ('a', 123);
Now, to cause the error, just run an update:
UPDATE test_table set amount = 1 where id = 'a';
You will find that it returns:
test_select
-------------
(0 rows)
Rather than what I expect:
UPDATE 1
When that rule should never fire (the id hasn't changed). If I change
the conditional of the rule to something that must always be false
(like false, or 1 = 0), it will still behave in this manner.
So am I doing something wrong or am I seeing a bug?
Thanks,
Thomas Meeks