CODEWITHSUNDEEP
postgresqlfunctionplpgsql
Laveena
Laveena

Reputation: 471

Simple Syntax error creating Postgres function with language plpgsql

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

Upvotes: 0

Views: 2419

Answers (1)

user330315
user330315

Reputation:

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.

Upvotes: 1

Related Questions