[HACKERS] INSTEAD OF trigger on VIEWs

classic Classic list List threaded Threaded
10 messages Options
Reply | Threaded
Open this post in threaded view
|

[HACKERS] INSTEAD OF trigger on VIEWs

--= Tono =--
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 [hidden email])
Reply | Threaded
Open this post in threaded view
|

Re: [HACKERS] INSTEAD OF trigger on VIEWs

Christopher Kings-Lynne
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 [hidden email])

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

               http://archives.postgresql.org
Reply | Threaded
Open this post in threaded view
|

Re: [HACKERS] INSTEAD OF trigger on VIEWs

Jan B.
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;
>>
>>


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
      subscribe-nomail command to [hidden email] so that your
      message can get through to the mailing list cleanly
Reply | Threaded
Open this post in threaded view
|

Re: [HACKERS] INSTEAD OF trigger on VIEWs

Christopher Kings-Lynne
> 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

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
      joining column's datatypes do not match
Reply | Threaded
Open this post in threaded view
|

Re: [HACKERS] INSTEAD OF trigger on VIEWs

Jan B.
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

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
    (send "unregister YourEmailAddressHere" to [hidden email])
Reply | Threaded
Open this post in threaded view
|

Re: [HACKERS] INSTEAD OF trigger on VIEWs

--= Tono =--
In reply to this post by --= Tono =--
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
<[hidden email]> 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
> [hidden email])
>

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

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings
Reply | Threaded
Open this post in threaded view
|

Re: [HACKERS] INSTEAD OF trigger on VIEWs

Russell Smith
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.

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
      joining column's datatypes do not match
Reply | Threaded
Open this post in threaded view
|

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

Jan B.
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.
>

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
      subscribe-nomail command to [hidden email] so that your
      message can get through to the mailing list cleanly
Reply | Threaded
Open this post in threaded view
|

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

Jeff Eckermann
""Jan B."" <[hidden email]> wrote in message news:[hidden email]...
>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 [hidden email] so that your
>      message can get through to the mailing list cleanly
>



---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

               http://archives.postgresql.org
Reply | Threaded
Open this post in threaded view
|

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

Tom Lane-2
In reply to this post by Jan B.
"Jan B." <[hidden email]> 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 [hidden email] so that your
      message can get through to the mailing list cleanly