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?

Aside: If `id` is the primary key, you can simplify to `group by t.id`. See: http://stackoverflow.com/questions/8684486/pgerror-error-aggregates-not-allowed-in-where-clause-on-a-ar-query-of-an-objec/8684512#8684512 — Erwin Brandstetter, Dec 20 '16 at 15:16

score 1 · Answer 1 · 2016-12-20T15:05:41.500

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

edited Dec 20 '16 at 15:05

answered Dec 20 '16 at 13:08

The added remark is incorrect. The cast to `integer` is necessary. `count(*)` returns `bigint`, which would raise an error. – Erwin Brandstetter Dec 20 '16 at 15:02
@ErwinBrandstetter: wouldn't that cast happen "automatically" because the return column is defined as `integer` in the `returns table()` part? – Dec 20 '16 at 15:13
`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: http://stackoverflow.com/a/21051215/939860 – Erwin Brandstetter Dec 20 '16 at 15:24

Error at create function

1 Answers1