ERROR: syntax error at or near "RETURN" , PostgreSQL function

Question

I am trying to write a function which returns a json data from my table by adding person id . I am using pgAdmin 4 for it.

CREATE FUNCTION public.getuserinfo(IN userid bigint DEFAULT 00000, OUT uinfo json)
RETURNS SETOF json
LANGUAGE 'sql'
VOLATILE 
AS $BODY$
SELECT info into uinfo FROM public.users Where uid=userid;
RETURN uinfo;
$BODY$;
ALTER FUNCTION public.getuserinfo(bigint)
 OWNER TO sun;
GRANT EXECUTE ON FUNCTION public.getuserinfo(bigint) TO sun 
WITH GRANT OPTION;
REVOKE ALL ON FUNCTION public.getuserinfo(bigint) FROM 
PUBLIC;
COMMENT ON FUNCTION public.getuserinfo(bigint)
  IS 'gets user info by id';

but i am getting this error.error image i searched on tutorials and another previous post this but got no help. thanks in advance.

Sami Kuhmonen · Accepted Answer · 2018-07-23 06:44:28Z

1

You’re not telling the function there’s a sequence of things, so it expects only one statement to be there and there’s two. Use BEGIN and END:

CREATE FUNCTION public.getuserinfo(IN userid bigint DEFAULT 00000, OUT uinfo json)
RETURNS SETOF json
VOLATILE 
AS $BODY$
BEGIN
  SELECT info into uinfo FROM public.users Where uid=userid;
  RETURN uinfo;
END;
$BODY$ LANGUAGE plpgsql;

Also the language probably should be PL/PGSQL

edited Jul 23, 2018 at 6:44

answered Jul 23, 2018 at 6:32

Sami Kuhmonen

31.4k9 gold badges67 silver badges81 bronze badges

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

8 Comments

Axel Over a year ago

i tried that before and it gives ERROR: syntax error at or near "SELECT" LINE 7: SELECT info into uinfo FROM public.users Where uid=userid; ^ error message

Sami Kuhmonen Over a year ago

@Axel and you didn’t have semicolon after BEGIN?

Axel Over a year ago

CREATE FUNCTION public.getuserinfo(IN userid bigint DEFAULT 00000, OUT uinfo json) RETURNS json LANGUAGE 'sql' AS $BODY$ BEGIN SELECT info into uinfo FROM public.users Where uid=userid; RETURN uinfo; END; $BODY$; this is my code now

Sami Kuhmonen Over a year ago

@Axel Also only now realized you have language as sql, it probably should be plpgsql since it’s not just sql statements

Axel Over a year ago

thank you very much @Sami Kuhmonen. i changed the language and removed the return statement and it is working fie.

|

Collectives™ on Stack Overflow

ERROR: syntax error at or near "RETURN" , PostgreSQL function

1 Answer 1

8 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related