[HACKERS] SELECTs inside of VIEWs (WAS: INSTEAD OF trigger on VIEWs)
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 [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] INSTEAD OF trigger on VIEWs
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 [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] INSTEAD OF trigger on VIEWs
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 [EMAIL PROTECTED])