postgresql: procedure to return the number of occurrences of given year and service

All we need is an easy explanation of the problem, so here it is.

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 "tb_orders" and has "created_dt" and "order_code" 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.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

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.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply