| From: | Enrico Sirola <enrico(dot)sirola(at)gmail(dot)com> | 
|---|---|
| To: | "pgsql-general(at)postgresql(dot)org General" <pgsql-general(at)postgresql(dot)org> | 
| Subject: | on delete rules returned rowcount | 
| Date: | 2008-01-25 09:06:52 | 
| Message-ID: | 5AECB7DF-5609-472C-BE16-FF2A390F458C@gmail.com | 
| Views: | Whole Thread | Raw Message | Download mbox | Resend email | 
| Thread: | |
| Lists: | 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.
| From | Date | Subject | |
|---|---|---|---|
| Next Message | User Map | 2008-01-25 09:18:09 | exporting postgre data | 
| Previous Message | Decibel! | 2008-01-25 06:59:25 | Re: PostgreSQL professionals group at LinkedIn.com |