on delete rules returned rowcount
От | Enrico Sirola |
---|---|
Тема | on delete rules returned rowcount |
Дата | |
Msg-id | 5AECB7DF-5609-472C-BE16-FF2A390F458C@gmail.com обсуждение исходный текст |
Список | pgsql-general |
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.
В списке pgsql-general по дате отправления: