Simple Syntax error creating Postgres function with language plpgsql

Question

I am getting a very small but irritating error while creating a Postgres function.

The function get executed when my language is sql but throws a syntax error

syntax error at or near "Select" LINE 14: Select * from x*

when I change the language to plpgsql, as I need to use control statements I need my language to be plpgsql not sql.

Here is the function defination

-- FUNCTION: public."HHMD"()
 DROP FUNCTION public."HHMD"();
 CREATE OR REPLACE FUNCTION public."HHMD"(
 )
 RETURNS TABLE(changesets character varying)
 LANGUAGE 'plpgsql'
 COST 100
 VOLATILE 
 ROWS 1000
 AS $BODY$
 Select * from x
 $BODY$;
 ALTER FUNCTION public."HHMD"()
 OWNER TO lke_new;
 COMMENT ON FUNCTION public."HHMD"()
 IS 'work!';

Answer 1

As documented in the manual a PL/pgSQL function needs (at least) a BEGIN and END block.

To return the result of a query from within a PL/pgSQL function, you need to use return query

So your function should look like this:

CREATE OR REPLACE FUNCTION public."HHMD"()
 RETURNS TABLE(changesets character varying)
 LANGUAGE plpgsql
 COST 100
 VOLATILE 
 ROWS 1000
AS $BODY$
BEGIN --<< this is missing
 -- return the result of a query
 return query
 Select * from x;
END; --<< the corresponding END for the BEGIN
$BODY$;

---

Unrelated, but:

The language name is an identifier, you should not put it in single quotes. Use language sql or language plpgsql instead.

• Laveena: Single quotes was generated by the NEW FUNCTION UI in pgadmin4