CODEWITHSUNDEEP
postgresqlstored-proceduresplpgsql
Samrat Das
Samrat Das

Reputation: 1918

Postgres PERFORM COUNT(*) always returning 0

I had a query in SQL for postgres to get the count

SELECT COUNT(*) AS count_variable FROM user WHERE something=something;

When I executed, it was returning the count. Then as per requirement this query was required inside a Postgres Function.When I used this query inside Function, Postgres replied

ERROR: query has no destination for result data
HINT: If you want to discard the results of a SELECT, use PERFORM instead.
CONTEXT: PL/pgSQL function myfuntion(integer,integer) line 11 at SQL statement

I searched for this error and found that, this happens if query returned null while using SELECT, But already I was getting value when executed directly on command line.

And also some posts told to discard it we should use PERFORM instead of SELECT. So my new query inside the same function was

PERFORM COUNT(*) AS count_variable FROM user WHERE something=something;

After this function started working, but count_variable is always zero. I am checking it using raise after PERFORM query.

raise notice 'count_variable: %', count_variable;

count_variable is declared as

DECLARE
  count_variable int;
  ...
BEGIN
  count_variable := 0;

Is there anything I am missing or doing wrong, or COUNT() function doesn't works inside function. If count() is not available, is their any alternative for counting rows. Somewhere I saw @@ROWCOUNT is also a variable to get row count, but it gives error.

Help will be highly appreciated.

Upvotes: 0

Views: 2186

Answers (1)

user330315
user330315

Reputation:

You don't want to discard the result of the select, so perform is the wrong choice. You want to store the result of the query in a variable, so you need an INTO clause:


DECLARE
  count_variable int;
  ...
BEGIN
  SELECT COUNT(*) INTO count_variable 
  FROM "user" 
  WHERE something=something;
  ...

Just because you give the column an alias that is the same name as a variable, does not mean the result is stored in that variable. The column names have no relation with variables.

This is all explained in detail in the manual. Especially the chapter Executing a Query with a Single-row Result

Upvotes: 2

Related Questions