How to create a function with postgres

Question

I'm trying to run this command according the documentation but it always returns 1, i.e. not the number of rows that I want

CREATE FUNCTION getQuantity() RETURNS integer $$
   select count(*) from table;
$$ LANGUAGE SQL;

select getQuantity();

Does anyone know if I'm doing something wrong?

You are not returning anything. Where's your RETURN statement? — SS_DBA
– SS_DBA, Commented May 24, 2017 at 18:13
@WEI_DBA you mean put return select count(*)... or save the select statement in a variable and return it? — Italo Lemos
– Italo Lemos, Commented May 24, 2017 at 18:24

klin · Accepted Answer · 2017-05-24 18:49:24Z

1

You missed a keyword as. Also, count(*) returns bigint, so:

create function get_quantity() 
returns bigint as $$
   select count(*) from my_table;
$$ language sql;

or

create function get_quantity() 
returns integer as $$
   select count(*)::int from my_table;
$$ language sql;

Test it in dbfiddle.

edited May 24, 2017 at 18:49

answered May 24, 2017 at 18:31

klin

123k15 gold badges240 silver badges262 bronze badges

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

3 Comments

Italo Lemos Over a year ago

Still return one yet, I try both

klin Over a year ago

Both functions work as expected, see the link in the modified answer. Maybe you should drop an old version of the function: drop function get_quatnity().

Italo Lemos Over a year ago

Yeah, after I drop it worked. It's something like cache in browser

Collectives™ on Stack Overflow

How to create a function with postgres

1 Answer 1

3 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Related