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.

Beguin

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? – Category

@Catcall: Good point. I included a row for empty months in my final edit. – Expedient

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. – Beguin

Answer 1

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.

Expedient

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

Wondering if anything you wrote is not supported in 8.3? – Beguin

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

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

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

OK: I did CREATE LANGUAGE plpgsql. Adding the function worked OK. When I do SELECT * FROM f_products_per_month(); I get another ERROR though. ERROR: function get_forecast_history(text, text) does not exist LINE 1: SELECT h.y, h.m, h.product, h.actual FROM get_forecast_hist... – Beguin

@Pyrite: that's an easy one. I amended my answer with additional info for that. – Expedient

@Pyrite: can it be that you defined the function with integer input (not text)? That is one of those details that should be in your question to begin with ... I amended my answer on that suspicion. – Expedient

It is, and I believe I did put it in the OP. – Beguin

@Pyrite: I am sorry, I missed that. Your question is just fine. Should work now. – Expedient

Getting this error now: ERROR: structure of query does not match function result type CONTEXT: PL/pgSQL function "f_products_per_month" line 12 at RETURN QUERY I will update my OP with the function definition prototype header. – Beguin

I apologize if I may have worded my question wrong by using the word TABLE. It is a CUSTOM TYPE in Postgres (I updated my original post). The whole function definition is very large (150 lines), and is somewhat confidential, so I can't really post it here. – Beguin

@Pyrite: What are the exact datatypes of the columns get_forecast_history() returns? (y, m, product, actual)? That bit is missing in the question - I re-checked ;). – Expedient

@Pyrite: we should be getting there now. See the amended version. – Expedient

Works perfectly now! I'd totally buy you a beer right now for all your effort. – Beguin

Forgot to say, THANK YOU. I also learned some stuff from this. – Beguin

@Pyrite: Cool :) You have put a thought in my mind now, I'll have a beer later. You can always upvote instead. – Expedient

I'll try to remember to upvote you when I have enough reputation to do so. I had just signed up today. – Beguin

There, I gave you the credit you deserve. See, told you I would :) – Beguin

@Pyrite: Merci. Good things come to those who wait. :) – Expedient