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: stackoverflow.com/questions/8684486/… — Erwin Brandstetter
– Erwin Brandstetter, Commented Dec 20, 2016 at 15:16

score 1 · Accepted Answer · 2016-12-20 15:05:41Z

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, 2016 at 15:05

answered Dec 20, 2016 at 13:08

user330315

Sign up to request clarification or add additional context in comments.

3 Comments

Erwin Brandstetter Over a year ago

The added remark is incorrect. The cast to integer is necessary. count(*) returns bigint, which would raise an error.

user330315 Over a year ago

@ErwinBrandstetter: wouldn't that cast happen "automatically" because the return column is defined as integer in the returns table() part?

Erwin Brandstetter Over a year ago

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

Collectives™ on Stack Overflow

Error at create function

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related