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)

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)

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

http://archives.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;
>>
>>


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

> 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

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 majordomo@postgresql.org)