Thread: merge in postgres trigger function
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
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 > > > >