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!';

user330315 · Accepted Answer · 2018-10-01 13:39:46Z

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.

Single quotes was generated by the NEW FUNCTION UI in pgadmin4

Collectives™ on Stack Overflow

Simple Syntax error creating Postgres function with language plpgsql

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related