Hi,
short summary of the problem follows :)
I'm writing an on delete rule for a view and I need to set the status
message (DELETE XXX) for
number of deleted tuples. Is it possible?
A brief, working use case follows:
I have a view restricting the access to a table, as the following:
create table test (a serial, b timestamptz default 'infinity', primary
key (a,b));
create view v_test as (select id from test where b='infinity');
when I "delete" values from the view I'd like to set the b field in
the test table
in order for the values to disappear from the view, as in the following:
create rule v_test as on delete to v_test do instead update test set
b=now() where a=OLD.a and b='infinity';
sps_test=# truncate test;
TRUNCATE TABLE
sps_test=# insert into test (a) values (nextval('test_a_seq'));
INSERT 0 1
sps_test=# insert into test (a) values (nextval('test_a_seq'));
INSERT 0 1
sps_test=# select * from v_test;
a
---
5
6
(2 rows)
sps_test=# delete from v_test where a=5;
DELETE 0
sps_test=# select * from v_test;
a
---
6
(1 rows)
sps_test=# select * from test;;
a | b
---+-------------------------------
6 | infinity
5 | 2008-01-25 09:55:53.179059+01
(2 rows)
This is pretty cool, it works. However if I delete directly from test:
delete from test where a=5;
sps_test=# delete from test where a=5;
DELETE 1
as you see above, here you get a 'DELETE 1', while when deleting from
the view, you get a 'DELETE 0'.
These messages are propagated back to the DB driver in the application
which sets a "rowcount" attribute
used from the application developers to know how many tuples have been
affected by the command, so here's
the question:
Is it possible to set the returned message? I need to return DELETE
<n. of tuples updated>, otherwise the
view + rules does not really behaves like a table and its practical
usability is compromised.
Thanks a lot in advance,
e.
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
