I am trying to write a Postgres function. I am doing it for the first time so don't feel annoyed if you find some silly mistakes:
CREATE FUNCTION proc_test(userId int)
returns void
begin
UPDATE pl_payroll SET remarks =viw.remarks from pl_payroll
diff,viwPayDifference viw
where diff.userid = cast(userId as varchar);
end;
It is giving an error like
syntax error at or near "begin"
What's wrong here?
SQL functions don't have BEGIN and END like plpgsql functions.
CREATE FUNCTION proc_test(userId int)
RETURNS void AS
$func$
UPDATE pl_payroll
SET remarks = viw.remarks
FROM pl_payroll diff, viwPayDifference viw
WHERE diff.userid = cast(userId as varchar);
$func$ LANGUAGE sql;
Your UPDATE also looks wrong per se.
pl_payroll to the additional tables.pl_payroll a second time.userId is not table-qualified and probably ambiguous.Check out the UPDATE syntax in the manual. Should be something like:
CREATE FUNCTION proc_test(userId int)
RETURNS void AS
$func$
UPDATE pl_payroll p
SET remarks = viw.remarks
FROM viwPayDifference viw
WHERE viw.userid = p.userId::varchar;
$func$ LANGUAGE sql;
As an aside: It looks highly suspicious that you have to cast diff.userId to varchar. Something off with your data types in your base tables?
