CODEWITHSUNDEEP
sqlpostgresqlstored-proceduressql-update
Kashif Imran
Kashif Imran

Reputation: 579

PostgreSQL sql function syntax error while writing function

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?

Upvotes: 1

Views: 1417

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656804

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.

  • There is no WHERE condition joining pl_payroll to the additional tables.
  • You don't need to join in pl_payroll a second time.
  • The second occurrence of 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?

Upvotes: 2

Related Questions