PostgreSQL generate_series() with SQL function as arguments

Question

I have a SQL function called get_forecast_history(integer,integer) that takes two arguments, a month and a year. The function returns a CUSTOM TYPE created with:

CREATE TYPE fcholder AS (y integer, m integer, product varchar, actual real);

The first line of the function definition is:

CREATE OR REPLACE FUNCTION get_forecast_history(integer, integer)
  RETURNS SETOF fcholder AS $$

Calling:

SELECT * FROM get_forecast_history(10, 2011);

For example produces the following table (the result type of the function is a table i.e. SETOF):

  y   m product  actual
---- -- -------- ------
2011 10 Product1  29
2011 10 Product2  10
2011 10 Product3  8
2011 10 Product4  0
2011 10 Product5  2

etc. (about 30 products total). This is the history for the given month.

I also have another query that generates a series of months:

SELECT to_char(DATE '2008-01-01'
            + (interval '1 month' * generate_series(0,57)), 'YYYY-MM-DD') AS ym

Which products a list like this:

ym
----------
2008-01-01
2008-02-01
2008-03-01
2008-04-01
...
2011-10-01

I need to somehow LEFT JOIN the results of the generate_series of year/month combinations on the function above by taking the results of the generate_series and passing them as arguments to the function. This way I'll get the results of the function, but for every year/month combination from the generate_series. At this point I'm stuck.

I'm using PostgreSQL 8.3.14.

The value '2008-01-01' isn't part of a series of months. It's part of a series of dates. Are you trying to make sure your final results aren't missing any months, even if there were no products or actuals during that month? — Mike Sherrill 'Cat Recall'
– Mike Sherrill 'Cat Recall', Commented Oct 31, 2011 at 16:20
@Catcall: Good point. I included a row for empty months in my final edit. — Erwin Brandstetter
– Erwin Brandstetter, Commented Oct 31, 2011 at 17:22
I actually intended for it to return all months in the given series. The custom function determines the value, and if it is zero, I still want it included. — Brandon
– Brandon, Commented Oct 31, 2011 at 18:26

Erwin Brandstetter · Accepted Answer · 2011-10-31 17:22:01Z

2

What you are trying to to could work like this:

Edit with additional info

CREATE OR REPLACE FUNCTION f_products_per_month()
  RETURNS SETOF fcholder AS
$BODY$
DECLARE
    r fcholder;
BEGIN

FOR r.y, r.m IN
    SELECT to_char(x, 'YYYY')::int4  -- AS y
          ,to_char(x, 'MM')::int4    -- AS m
    FROM  (SELECT '2008-01-01 0:0'::timestamp
        + (interval '1 month' * generate_series(0,57)) AS x) x
LOOP
    RETURN QUERY
    SELECT *    -- use '*' in this case to stay in sync
    FROM   get_forecast_history(r.m, r.y);

    IF NOT FOUND THEN
       RETURN NEXT r;
    END IF;
END LOOP;

END;
$BODY$
  LANGUAGE plpgsql;

Call:

SELECT * FROM f_products_per_month();

Major points:

Final edit to include an otherwise empty row for months without products.
You wrote "LEFT JOIN", but that's not how it can work.
There are several ways to do this, but RETURN QUERY is the most elegant.
Use the same return type as your function get_forecast_history() uses.
Avoid naming conflicts with the OUT parameters by table-qualifying the column names (not applicable any more in the final version).
Don't use DATE '2008-01-01', use a timestamp like I did, it has to be converted for to_char() anyway. Less casting, performs better (not that it matters much in this case).
'2008-01-01 0:0'::timestamp and timestamp '2008-01-01 0:0' are just two syntax variants doing the same.
For older versions of PostgreSQL the language plpgsql is not installed by defualt. You may have to issue CREATE LANGUAGE plpgsql; once in your database. See the manual here.

You could probably simplify your two functions into one query or function if you wanted.

edited Oct 31, 2011 at 17:22

answered Oct 31, 2011 at 15:32

Erwin Brandstetter

668k159 gold badges1.2k silver badges1.3k bronze badges

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

20 Comments

Brandon Over a year ago

SQL error: ERROR: syntax error at or near "TABLE" LINE 2: RETURNS TABLE( ^

Brandon Over a year ago

Wondering if anything you wrote is not supported in 8.3?

Brandon Over a year ago

OK, thanks, standing by. (Sadly, I do not have the option to upgrade, unless IT decides to get ambitious.

Erwin Brandstetter Over a year ago

@Pyrite: Oh, wait .. version 8.3.14 .. let me rephrase that. Amended my answer with a variant for your older version.

Brandon Over a year ago

ERROR: language "plpgsql" does not exist. cries. Let me read about that one.

|

Collectives™ on Stack Overflow

PostgreSQL generate_series() with SQL function as arguments

1 Answer 1

Edit with additional info

Major points:

20 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Edit with additional info

Major points:

20 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related