Re: SELECTs inside of VIEWs (WAS: INSTEAD OF trigger on VIEWs)

""Jan B."" <jan(at)monso(dot)de> wrote in message news:4293055C(dot)6050409(at)monso(dot)de(dot)(dot)(dot)
>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.

FWIW, I believe that the current development version has code which fixes
this problem (i.e. allows for no return at all), along with some other
enhancements. Check the archives of the pgsql-hackers list for more
information.

Last I heard, feature freeze for version 8.1 is expected in July, so this
feature may be available in a stable version before very long.

>
> 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.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
>