postgresql: procedure to return the number of occurrences of given year and service I am creating a procedure to return the number of times a given service is done in a year. The procedure cannot return a NULL value. The table I am taking the data from is called &quot;tb_orders&quot; and has &quot;created_dt&quot; and &quot;order_code&quot; as columns; for the datetime the service was provided and the code of the service itself.
So far I've got this:
CREATE FUNCTION catalog_yearly_orders
  (year, order_code) RETURNS numeric AS $$
DECLARE
  n_service integer NOT NULL,
  year YEAR(tb_orders.created_dt)%TYPE;
BEGIN
  SELECT order_code INTO order_code
  FROM tb_orders
  WHERE order_code = order_code;
  n_service = n_service + 1;
  RETURN n_service;
END;
$$ LANGUAGE plpgsql;

I think what I am missing is some command, between BEGIN-END, for the parameters to match the tuples I am looking for so that I get the ones which coincide with year and service code.
Assuming the tb_orders table has an id, would you not be able to do something like the following?
RETURN (SELECT COUNT(tbo.id)
          FROM tb_orders tbo
         WHERE YEAR(tbo.created_dt) = year AND tbo.order_code = order_code);

In your DECLARE you are overwriting the year value, and I'm not at all sure what n_service is supposed to do. If all you want is a count, then COUNT() is all you need. Mind you, using YEAR() in the WHERE clause is not great, but it's sufficient for the sake of this example.

Note: The syntax may be a bit off. I'm a bit rusty with stored procedures in PostgreSQL.