INSTEAD OF trigger on VIEWs

You can probably just create an INSTEAD rule on the view...

Chris

--= Tono =-- wrote:

I have a similar problem and already considered using RULEs, but I
encountered the problem, that I did not find any way to execute
procedures from RULEs without using SELECT, which creates always a
result set being passed to the application invoking the INSERT, UPDATE
or DELETE, even if the function is declared VOID. This is causing
trouble when using asynchronous command processing.

Christopher Kings-Lynne wrote:

I have a similar problem and already considered using RULEs, but I
encountered the problem, that I did not find any way to execute
procedures from RULEs without using SELECT, which creates always a
result set being passed to the application invoking the INSERT, UPDATE
or DELETE, even if the function is declared VOID. This is causing
trouble when using asynchronous command processing.

The solution then is for us to get around to implementing procedures,
rather than functions, in PostgreSQL I think.

Chris

Christopher Kings-Lynne wrote:

I have a similar problem and already considered using RULEs, but I
encountered the problem, that I did not find any way to execute
procedures from RULEs without using SELECT, which creates always a
result set being passed to the application invoking the INSERT,
UPDATE or DELETE, even if the function is declared VOID. This is
causing trouble when using asynchronous command processing.

The solution then is for us to get around to implementing procedures,
rather than functions, in PostgreSQL I think.

Chris

Yes, I think that this would be a good way to solve the problem, but is
it planned to implement procedures in near future?
What about extending the SQL command set by an "INVOKE" command, which
invokes a function and discards the result?

Jan Behrens

I have tried using INSTEAD rules but there are some
conditional logic that needs to happen inside the rule
(performing counts, getting and storing the primary
key of the master record etc.). AFAIK, rules only
allows conditional logic to be check prior to
execution of the rule and not inside the rule itself.
One way to get around this is to allow calling a
stored procedure inside the rule. This stored
procedure should have full access of NEW.* (and OLD.*
in the case of UPDATE and DELETE). This way the
manual INSERT, UPDATE or DELETE on the actual tables
can be performed from inside the stored procedure.

--- Christopher Kings-Lynne
<chriskl@familyhealth.com.au> wrote:

You can probably just create an INSTEAD rule on the
view...

Chris

--= Tono =-- wrote:

Is there any plans to create an INSTEAD OF trigger

on

VIEWS? I have view which consists of a master

and

detail table. When a row is inserted into the

view,

the view needs to figure out if the master record
already exsists. If the record does not exists in

the

master table, then insert into the master and the
detail table. If the record already exists in the
master, just insert into detail table.

Conversely, if

a delete record is performed on the view, the view
needs to figure out if it only needs to delete

from

the detail table, or should it also delete from

the

master table when all the detail records are

already

deleted. In Oracle this is easily done using

INSTEAD

OF triggers. INSTEAD OF triggers can only be

created

for VIEWs. The purpose of it is to "short-circuit"

the

event (INSERT, UPDATE or DELETE) and perform

whatever

is specified in the trigger.

CREATE OR REPLACE TRIGGER schema.trigger_name

INSTEAD

OF INSERT ON object_name
BEGIN
-- Perform the following instead --
END;

__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam

protection around

http://mail.yahoo.com

---------------------------(end of

broadcast)---------------------------

TIP 2: you can get off all lists at once with the

unregister command

(send "unregister YourEmailAddressHere" to

majordomo@postgresql.org)

__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com

On Tue, 24 May 2005 01:26 am, --= Tono =-- wrote:

I have tried using INSTEAD rules but there are some
conditional logic that needs to happen inside the rule
(performing counts, getting and storing the primary
key of the master record etc.). AFAIK, rules only
allows conditional logic to be check prior to
execution of the rule and not inside the rule itself.
One way to get around this is to allow calling a
stored procedure inside the rule. This stored
procedure should have full access of NEW.* (and OLD.*
in the case of UPDATE and DELETE). This way the
manual INSERT, UPDATE or DELETE on the actual tables
can be performed from inside the stored procedure.

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.

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:

""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.

"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