PostgreSQLgenerate_series()以SQL函数作为参数

我有一个名为的 SQL 函数get_forecast_history(integer,integer)这需要两个参数，一个月和一年。该函数返回使用以下命令创建的自定义类型：

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

函数定义的第一行是：

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

Calling:

SELECT * FROM get_forecast_history(10, 2011);

例如，生成下表（函数的结果类型是表，即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
  

等（共约30种产品）。这是给定月份的历史记录。

我还有另一个生成一系列月份的查询：

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

哪些产品的列表如下：

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

我需要以某种方式LEFT JOIN的结果generate_series通过取以下结果，对上述函数进行年/月组合generate_series并将它们作为参数传递给函数。这样我就可以得到函数的结果，但是对于每个年/月的组合generate_series。此时我被困住了。

我正在使用 PostgreSQL 8.3.14。

你想要做的事情可以像这样工作：

编辑附加信息

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();

主要观点：

• 最终编辑包括几个月没有产品的空行。
• 您写了“LEFT JOIN”，但这不是它的工作原理。
• 有几种方法可以做到这一点，但是RETURN QUERY是最优雅的。
• 使用与函数 get_forecast_history() 使用的相同的返回类型。
• 通过对列名进行表限定来避免与 OUT 参数的命名冲突（在最终版本中不再适用）。
• 不要使用DATE '2008-01-01'，像我一样使用时间戳，无论如何它都必须转换为 to_char() 。更少的铸造，性能更好（在这种情况下这并不重要）。
• '2008-01-01 0:0'::timestamp and timestamp '2008-01-01 0:0'只是两个语法变体做同样的事情。
• 对于旧版本的 PostgreSQL，默认情况下不安装语言 plpgsql。您可能需要发出CREATE LANGUAGE plpgsql;一旦进入您的数据库。看手册在这里.

如果需要，您可以将两个函数简化为一个查询或函数。