Postgres: unable to create function

Question

I need to write a very simple function. I will pass three parameters(employeeid, employeetitle and employeeaddress). If passed employeeid is equal to 0, a new record will be inserted into the table. If the employeeid passed is greater than 0 the record will be updated. In both the cases employeeid will be returned as result(id is auto increment value in employee table). I am using pgAdmin and here is the code I have written:

CREATE FUNCTION public.addupdateemployee(IN employeeid bigint, IN employeetitle text, IN employeeaddress text)
RETURNS bigint
LANGUAGE 'plpgsql'

AS $BODY$
BEGIN
DECLARE affectedrecordid bigint = 0
IF employeeid = 0 THEN
INSERT INTO employeemaster(title, address)
values(employeetitle, employeeaddress)
RETURNING id INTO affectedrecordid;
RETURN affectedrecordid;
ELSE
UPDATE employeemaster 
SET title = employeetitle,
address = employeeaddress
WHERE id = employeeid  
RETURN employeeid;
END IF
END
$BODY$;

When I execute the above statement I am getting the following error:

ERROR:  syntax error at or near "IF"
LINE 8: IF employeeid = 0 THEN
        ^
SQL state: 42601
Character: 218

I am unable to figure out the issue. Can someone help me fixing the above function?

user330315 · Accepted Answer · 2019-02-19 09:51:40Z

2

declare needs to go before the first begin. And you are missing the ; for the variable declaration and after the IF block. The language name is an identifier and should not be put in single quotes. And the UPDATE statement needs a RETURNING not a RETURN

So it should be:

CREATE FUNCTION public.addupdateemployee(IN employeeid bigint, IN employeetitle text, IN employeeaddress text)
  RETURNS bigint
  LANGUAGE plpgsql
AS $BODY$
DECLARE 
  affectedrecordid bigint = 0;
begin  
  IF employeeid = 0 THEN
    INSERT INTO employeemaster(title, address)
    values(employeetitle, employeeaddress)
    RETURNING id INTO affectedrecordid;
    RETURN affectedrecordid;
  ELSE
    UPDATE employeemaster 
      SET title = employeetitle,
        address = employeeaddress
    WHERE id = employeeid  
    RETURNING employeeid;
  END IF;
END;
$BODY$;

answered Feb 19, 2019 at 9:51

user330315

Sign up to request clarification or add additional context in comments.

1 Comment

astm1982 Over a year ago

Thanks for the help!

Collectives™ on Stack Overflow

Postgres: unable to create function

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related