From: | Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> |
---|---|
To: | Leon Match <leon(dot)match(at)convergia(dot)net> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: merge in postgres trigger function |
Date: | 2011-06-18 03:10:10 |
Message-ID: | BANLkTinch+F1Os4eM9a=o40pfD+n1ywRWg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hello
PostgreSQL doesn't support MERGE statement yet
Regards
Pavel Stehule
2011/6/17 Leon Match <leon(dot)match(at)convergia(dot)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(dot)match(at)convergia(dot)net
>
>
>
>
From | Date | Subject | |
---|---|---|---|
Next Message | Bruce Momjian | 2011-06-18 03:54:03 | Re: pg_upgrade only to 9.0 ? |
Previous Message | Tom Lane | 2011-06-17 23:25:16 | Re: Odd performance difference in check constraint : SQL(slow) vs plpgsql(fast) |