Error at create function

Question

I try to create function

CREATE OR REPLACE FUNCTION public.my_sql_function3(IN inputval integer)
 RETURNS TABLE("ID" integer, name character varying, cnt integer) AS
$BODY$
 select t.id, t.name, CAST(count(*) AS INTEGER)
 from test t
 where t.id < inputval
 group by t.id, t.name $BODY$
 LANGUAGE plpgsql VOLATILE;

and get error:

ERROR: syntax error at or near "select"

LINE 4: select t.id, t.name, CAST(count(*) AS INTEGER)

How to fix it?

• Erwin Brandstetter: Aside: If id is the primary key, you can simplify to group by t.id. See: stackoverflow.com/questions/8684486/…

Answer 1

You have defined the function to be a PL/pgSQL function but your syntax is for a plain SQL function.

You need to use

LANGUAGE sql

• Erwin Brandstetter: The added remark is incorrect. The cast to integer is necessary. count(*) returns bigint, which would raise an error.
• user330315: @ErwinBrandstetter: wouldn't that cast happen "automatically" because the return column is defined as integer in the returns table() part?
• Erwin Brandstetter: CREATE FUNCTION accepts data types where at least an implicit cast is registered (like between varchar and text). But the cast from bigint to int is only assignment (potential overflow!). Details: stackoverflow.com/a/21051215/939860