PostgreSQL generate_series()以SQL函数作为参数

Question

我有一个名为get_forecast_history(integer,integer)的SQL函数，它接受两个参数，一个月和一个年。该函数返回一个使用

创建的CUSTOM TYPE

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 $$

调用:

SELECT * FROM get_forecast_history(10, 2011);

例如，

生成以下表(函数的结果类型是表，即SETOF):

<>之前Y m乘积---- -- -------- ------2011年10月1日2011年10月10日2011年10月3日2011年10月4日2011年10月5日之前

等。(共约30个产品)。这是给定月份的历史记录。

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

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

哪些产品有这样的列表:

<>之前ym----------2008-01-012008-02-012008-03-012008-04-01…2011-10-01之前

我需要通过获取generate_series的结果并将它们作为参数传递给函数，以某种方式LEFT JOIN年/月组合的generate_series的结果。这样我将得到函数的结果，但是对于generate_series的每个年/月组合。在这一点上，我被卡住了。

我用的是PostgreSQL 8.3.14

Answer 1

你要做的可能是这样的:

使用附加信息编辑

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;

调用:

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和timestamp '2008-01-01 0:0'只是两个语法变体做同样的事情。
• 对于旧版本的PostgreSQL，默认不安装plpgsql语言。您可能必须在数据库中发布一次CREATE LANGUAGE plpgsql;。

如果你愿意，你可以把这两个函数简化成一个查询或函数。