Postgres - How do I call a stored plpgsql function, have I made a mistake in the function itself?

Question

I Have written a Postgres function, using plpgsql, but get a 'syntax error, unexpected character' when I call it using:

PERFORM create_user('a', 'b', 'c');

Here is the Postgres function definition, copied from pgAdmin:

CREATE OR REPLACE FUNCTION create_user(_username character varying, _passwordhash character varying, _email character varying)
RETURNS integer AS
$BODY$
BEGIN

INSERT INTO users(id, username, passwordhash, email) VALUES (DEFAULT, _username, _passwordhash, _email) RETURNING id;


END;$BODY$
LANGUAGE plpgsql VOLATILE SECURITY DEFINER
COST 100;
ALTER FUNCTION create_user(character varying, character varying, character varying)
OWNER TO postgres;

Can anyone identify what I have done wrong here?

Also, I am trying to access it in node.js through 'pg', but I have not been able to call the function without a syntax error from pgAdmin anyway.

Answer 1

Why don't you try your function like below; by declaring a INT variable and then capture the generated id sequence into the variable. Finally return that variable.

In some Postgres forum post I had found that the construct RETURNING id is not supported in all versions. so, you may like to circumvent it other way.

CREATE OR REPLACE FUNCTION create_user(_username character varying,
                                       _passwordhash character varying, 
                                       _email character varying)
RETURNS integer AS
$BODY$
DECLARE retvar integer; <-- declare the variable
BEGIN

  INSERT INTO users(id, username, passwordhash, email) 
  VALUES (DEFAULT, _username, _passwordhash, _email) RETURNING id into retvar;
                                                             <-- get return id
return retvar; <-- return the variable
END;
$BODY$