SELECTs inside of VIEWs (WAS: INSTEAD OF trigger on VIEWs) - Mailing list pgsql-hackers

From Jan B.
Subject SELECTs inside of VIEWs (WAS: INSTEAD OF trigger on VIEWs)
Date
Msg-id 4293055C.6050409@monso.de
Whole thread Raw
In response to Re: INSTEAD OF trigger on VIEWs  (Russell Smith <mr-russ@pws.com.au>)
Responses Re: SELECTs inside of VIEWs (WAS: INSTEAD OF trigger on VIEWs)  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-hackers
I tried using SELECTs inside of RULEs, but as I already explained in 
this mail thread, the problem is, that a SELECT creates a result set, 
which can not be discarded in SQL. This makes trouble when using 
asynchronous command processing.

I have tried to modify my application in order to get a workaround, and 
noticed the following behaviour:

If there is one SELECT invoked by an INSERT, UPDATE or DELETE RULE, the 
result table of the select will be passed to the application. The 
command status (cmdStatus, i.e. "INSERT 141314 1") will be carried by 
this result set. If there are multiple SELECTs invoked by RULEs, there 
are multiple result sets passed to the application. I tested the 
behaviour and found out that all result sets carry an empty "" string as 
a cmdStatus, but the last one carries the actual cmdStatus of the 
INSERT, UPDATE or DELETE.

The documentation at 
http://www.postgresql.org/docs/8.0/interactive/rules-status.html does 
not give a hint, whether this is the indended behaviour or not.

Does anyone know, if it is intended that one query can create multiple 
result tables with some of them carrying an empty string as cmdStatus? 
Perhaps this is a bug?

Note: Using psql to test this behaviour will not give the same results, 
as the command status is not displayed by psql if there is a result 
table. If there are multiple result tables, only the last result table 
is printed out. PQexec of libpq also discards all, but the last result.


Jan Behrens



Russell Smith wrote:
> On Tue, 24 May 2005 01:26 am, --= Tono =-- wrote:
>  
> Would it be possible to add an INSTEAD OF rule that calls
> a function.  You could then use that function as the trigger
> you wanted.  I'm not even sure if this is possible.
> 
> DO INSTEAD SELECT * FROM function(rowtype);
> 
> Regards
> 
> Russell Smith. 
> 


pgsql-hackers by date:

Previous
From: Russell Smith
Date:
Subject: Re: INSTEAD OF trigger on VIEWs
Next
From: Gaetano Mendola
Date:
Subject: 8.0.x RPM issues