Thread: INSTEAD OF trigger on VIEWs

INSTEAD OF trigger on VIEWs

From
--= Tono =--
Date:
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 


Re: INSTEAD OF trigger on VIEWs

From
Christopher Kings-Lynne
Date:
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)


Re: INSTEAD OF trigger on VIEWs

From
"Jan B."
Date:
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:

> 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;
>>
>>



Re: INSTEAD OF trigger on VIEWs

From
Christopher Kings-Lynne
Date:
> 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


Re: INSTEAD OF trigger on VIEWs

From
"Jan B."
Date:
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


Re: INSTEAD OF trigger on VIEWs

From
--= Tono =--
Date:
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 


Re: INSTEAD OF trigger on VIEWs

From
Russell Smith
Date:
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. 


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

From
"Jan B."
Date:
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. 
> 


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

From
"Jeff Eckermann"
Date:
""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/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@postgresql.org so that your
>      message can get through to the mailing list cleanly
> 




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

From
Tom Lane
Date:
"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