How make a COUNT in function of PostgreSQL?

Question

I have a table in PostgreSQL, table1, with 5 columns (col_a, col_b, col_c, col_d, col_e). col_a is timestamp type and col_b is integer_type. I want to make a function with two parameters that returns count of col_b.

CREATE or REPLACE FUNCTION first_count (year schema.table1.col_a%type, col_b schema.table1.col_b%type)
    RETURNS void AS 
$$

DECLARE
 contador integer; 
 

BEGIN
    SELECT COUNT(col_b)  INTO contador
                FROM schema.table1
                WHERE schema.table1.col_b = col_b AND EXTRACT(year FROM 
                schema.table1.col_a) = year;
END;
$$
LANGUAGE plpgsql;

This works but when I try to pass parameters it gives me an error.

My aim with function is get the result like this query:

SELECT COUNT(col_b)
FROM schema.table1
WHERE col_b = 2216 AND EXTRACT(year FROM schema.table1.col_a) = 2009;

@LaurenzAlbe thanks for your answer, I tryied to use INTO but I don't get it. — Raq
– Raq, Commented May 5, 2021 at 16:36
INTO is spelled out here INTO Look at the example function at the bottom of the section(ignore the STRICT portion). If that does not make sense, show us in your question what you attempted to do. — Adrian Klaver
– Adrian Klaver, Commented May 5, 2021 at 16:48
Thanks for your answer, I edited my question using INTO. The function work but I don't get the count. — Raq
– Raq, Commented May 5, 2021 at 17:00

score 1 · Accepted Answer · 2021-05-05 18:14:24Z

1

Your function doesn't return anything, that's why you get no result.

You need to change it to returns bigint, and you don't need PL/pgSQL for this:

CREATE or REPLACE FUNCTION first_count (year int, col_b schema.table1.col_b%type)
    RETURNS bigint --<< here
AS 
$$
  SELECT COUNT(col_b)
  FROM schema.table1
  WHERE schema.table1.col_b = col_b 
   AND EXTRACT(year FROM schema.table1.col_a) = year;
$$
language sql;

edited May 5, 2021 at 18:14

answered May 5, 2021 at 17:05

user330315

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

4 Comments

Raq Over a year ago

Thanks, I tried it but I get the next error: operator does not exist: double precision = timestamp without time zone

user330315 Over a year ago

That is a different question. I assumed the datatypes of your parameters matches the columns.

user330315 Over a year ago

@Raq: you probably just need to change the type of the year parameter

Raq Over a year ago

Thanks, the year parameter is timestamp, how can I change it?

Belayer · Accepted Answer · 2021-05-05 21:32:17Z

The same way you extracted from the table column. So :

SELECT COUNT(col_b)  
  INTO contador
  FROM schema.table1
 WHERE schema.table1.col_b = first_count.col_b 
   AND EXTRACT(year FROM schema.table1.col_a) = 
       extract(year from year) ;

This also shows why year is a poor chance for the column,variable, or parameter name. There are 2 considerations for your parameter names. First, it is poor practice using the same name for parameters and table column names. Without proper qualification Postgres will always assume the column name in any SQL statement. So the predicate: where schema.table1.col_b = col_b is essentially the same as where true. A very common convention to avoid this is prefixing parameters with "p_", So p_col_a. Secondly, while Postgres does not consider year a reserved word it is a SQL standard reserved word. Not to mention the possible confusion with predicates like

extract(year from year)

Postgres will keep it straight by context; will you and other developers?

Collectives™ on Stack Overflow

How make a COUNT in function of PostgreSQL?

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related