PostgreSQL Hacks - SELECTs inside of VIEWs (WAS: INSTEAD OF trigger on VIEWs)

Author

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

Jan B.

2005-05-24, 7:24 am

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/...les-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@postgresql
.org so that your
message can get through to the mailing list cleanly

Jeff Eckermann

2005-05-24, 9:23 am

""Jan B."" <jan@monso.de> wrote in message news:4293055C.6050409@monso.de...
>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/...les-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:
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql
.org so that your
> message can get through to the mailing list cleanly
>

Tom Lane

2005-05-24, 9:23 am

"Jan B." <jan@monso.de> writes:
> 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?

Yes it is, and no it isn't. Check the archives --- there was extensive
discussion of what multi-command rules should return, a couple years
back when we settled on the current behavior.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql
.org so that your
message can get through to the mailing list cleanly

Sponsored Links