on delete rules returned rowcount

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: Raw Message | Whole Thread | 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.

Browse pgsql-general by date

  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