pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedcalling function from rule
5+ messages / 2 participants
[nested] [flat]
* calling function from rule
@ 2003-01-23 10:51 Tambet Matiisen <t.matiisen@aprote.ee>
0 siblings, 1 reply; 5+ messages in thread
From: Tambet Matiisen @ 2003-01-23 10:51 UTC (permalink / raw)
To: pgsql-sql
I have a view and update rule on it, which updates another table. Now I would like to call a function, after update of the table is done. Also the function must be called in the same transaction as update. I tried to define an additional update rule on the view, but doing a SELECT in update rule spoils the affected records count:
hekotek=# create table a(id integer);
CREATE TABLE
hekotek=# create view v as select * from a;
CREATE VIEW
hekotek=# create rule r as on update to v do instead update a set id = new.id where id = old.id;
CREATE RULE
hekotek=# create rule r1 as on update to v do select 1;
CREATE RULE
hekotek=# insert into a values (1);
INSERT 1194985 1
hekotek=# update v set id = 2;
?column?
----------
1
(1 row)
hekotek=# drop rule r1 on v;
DROP RULE
hekotek=# update v set id = 3;
UPDATE 1
It's important to me to have correct affected records count returned. I cannot use triggers, because views can't have triggers. I also cannot define the trigger on the table, because the function must be called only when updated through the view. I think I could define the rule to call the function in update query for some dummy table: update dummy set field = function(parameters). But is there a better way?
Tambet
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: calling function from rule
@ 2003-01-23 15:30 Tom Lane <tgl@sss.pgh.pa.us>
parent: Tambet Matiisen <t.matiisen@aprote.ee>
0 siblings, 0 replies; 5+ messages in thread
From: Tom Lane @ 2003-01-23 15:30 UTC (permalink / raw)
To: Tambet Matiisen <t.matiisen@aprote.ee>; +Cc: pgsql-sql
"Tambet Matiisen" <t.matiisen@aprote.ee> writes:
> I have a view and update rule on it, which updates another table. Now I would like to call a function, after update of the table is done. Also the function must be called in the same transaction as update. I tried to define an additional update rule on the view, but doing a SELECT in update rule spoils the affected records count:
Try 7.3, we changed the rules about returned records count.
http://www.ca.postgresql.org/users-lounge/docs/7.3/postgres/rules-status.html
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: calling function from rule
@ 2003-01-23 15:55 Tambet Matiisen <t.matiisen@aprote.ee>
0 siblings, 1 reply; 5+ messages in thread
From: Tambet Matiisen @ 2003-01-23 15:55 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
>
> "Tambet Matiisen" <t.matiisen@aprote.ee> writes:
> > I have a view and update rule on it, which updates another
> table. Now I would like to call a function, after update of
> the table is done. Also the function must be called in the
> same transaction as update. I tried to define an additional
> update rule on the view, but doing a SELECT in update rule
> spoils the affected records count:
>
> Try 7.3, we changed the rules about returned records count.
> http://www.ca.postgresql.org/users-lounge/docs/7.3/postgres/ru
> les-status.html
>
I have 7.3. When rule and action are the same, everything works fine.
Doing an insert in update rule and opposite are OK too. Problem is,
when I do select in insert/update/delete rule. Then the result of
select is returned instead of command status, even if the select
is done in non-instead rule and there is unconditional instead rule.
See the example in my previous mail.
Tambet
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: calling function from rule
@ 2003-01-23 17:00 Tom Lane <tgl@sss.pgh.pa.us>
parent: Tambet Matiisen <t.matiisen@aprote.ee>
0 siblings, 0 replies; 5+ messages in thread
From: Tom Lane @ 2003-01-23 17:00 UTC (permalink / raw)
To: Tambet Matiisen <t.matiisen@aprote.ee>; +Cc: pgsql-sql
"Tambet Matiisen" <t.matiisen@aprote.ee> writes:
>> Try 7.3, we changed the rules about returned records count.
> I have 7.3. When rule and action are the same, everything works fine.
> Doing an insert in update rule and opposite are OK too. Problem is,
> when I do select in insert/update/delete rule. Then the result of
> select is returned instead of command status, even if the select
> is done in non-instead rule and there is unconditional instead rule.
Oh, I think your complaint is really about the fact that psql doesn't
print the command status if it got any tuples (or even just a tuple
descriptor) in the result series. AFAICT the information returned by
the backend is sensible in this situation: the "UPDATE 1" status message
*is* returned and is available from PQcmdStatus. psql is just choosing
not to print it. I'm not sure that that's wrong, though.
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: calling function from rule
@ 2003-01-24 08:39 Tambet Matiisen <t.matiisen@aprote.ee>
0 siblings, 0 replies; 5+ messages in thread
From: Tambet Matiisen @ 2003-01-24 08:39 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
> -----Original Message-----
> From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
> Sent: Thursday, January 23, 2003 7:01 PM
> To: Tambet Matiisen
> Cc: pgsql-sql@postgresql.org
> Subject: Re: [SQL] calling function from rule
>
>
> "Tambet Matiisen" <t.matiisen@aprote.ee> writes:
> >> Try 7.3, we changed the rules about returned records count.
>
> > I have 7.3. When rule and action are the same, everything
> works fine.
> > Doing an insert in update rule and opposite are OK too. Problem is,
> > when I do select in insert/update/delete rule. Then the result of
> > select is returned instead of command status, even if the select
> > is done in non-instead rule and there is unconditional instead rule.
>
> Oh, I think your complaint is really about the fact that psql doesn't
> print the command status if it got any tuples (or even just a tuple
> descriptor) in the result series. AFAICT the information returned by
> the backend is sensible in this situation: the "UPDATE 1"
> status message
> *is* returned and is available from PQcmdStatus. psql is
> just choosing
> not to print it. I'm not sure that that's wrong, though.
>
Thanks, I only tested it with psql and got worried.
Tambet
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2003-01-24 08:39 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-01-23 10:51 calling function from rule Tambet Matiisen <t.matiisen@aprote.ee>
2003-01-23 15:30 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-01-23 15:55 Re: calling function from rule Tambet Matiisen <t.matiisen@aprote.ee>
2003-01-23 17:00 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-01-24 08:39 Re: calling function from rule Tambet Matiisen <t.matiisen@aprote.ee>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox