Hello
PostgreSQL doesn't support MERGE statement yet
Regards
Pavel Stehule
2011/6/17 Leon Match <leon.match@convergia.net>:
> Hello,
>
>
>
> I am trying to move few objects to postgres from oracle.
>
>
>
> I have an issue with a trigger, which has a merge inside?
>
>
>
> Here is my code:
>
> BEGIN
>
> MERGE INTO Requests r
>
> using (select
> new.web_form_id web_form_id,
>
>
> new.form_type form_type,
>
>
> new.submit_date submit_date,
>
>
> new.email email,
>
>
> new.custom_fields custom_fields
>
>
> from DUAL) w
>
> on
> (r.request_id = new.web_form_id)
>
> when not matched
>
> THEN
>
>
> insert (
>
>
> r.request_id,
>
>
> r.form_type,
>
>
> r.submit_date,
>
>
> r.request_email,
>
>
> r.request_description
>
>
> )
>
> values (
>
>
> w.web_form_id,
>
>
> w.form_type,
>
>
> w.submit_date,
>
>
> w.email,
>
>
> w.custom_fields
>
>
> )
>
> when matched
>
> then
>
> update set
>
> form_type =
> NVL (w.form_type, r.form_type),
>
> submit_date
> = NVL (w.submit_date, r.submit_date),
>
>
> request_email = NVL (w.email, r.request_email),
>
>
> request_description = NVL (w.custom_fields, r.request_description);
>
> end if;
>
> END;
>
>
>
> I receive an error – “Requests is not a known variable, Line 3: MERGE INTO
> Requests r”
>
>
>
> But “Requests” is a table is not a variable!
>
> What is wrong with my statement? May be Merge is not legal in postgres
> trigger functions?
>
> What would be the way around?
>
>
>
> Thank you,
>
> Leon
>
> leon.match@convergia.net
>
>
>
>