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 по дате отправления:

Предыдущее
От: Tom Lane
Дата:
Сообщение: Re: can't create index with 'dowcast' row
Следующее
От: Simon Riggs
Дата:
Сообщение: Re: [ADMIN] Backup