Re: merge in postgres trigger function

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