Thread: INSTEAD OF trigger on VIEWs
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
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)
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; >> >>
> 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: > 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. >
""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 >
"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