PostgreSQL generate_series（）与SQL函数作为参数 [英] PostgreSQL generate_series() with SQL function as arguments

问题描述

我有一个名为 get_forecast_history（integer，integer）的SQL函数，它有两个参数，一个月和一年。该函数返回一个创建自定义类型：

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

函数定义的第一行是：

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

调用：

SELECT * FROM get_forecast_history(10, 2011);

例如生成下表（函数的结果类型是一个表，即 SETOF ）：

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

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

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

我需要以某种方式 LEFT JOIN 上述功能的年/月组合的 generate_series 的结果，取为 generate_series 并将它们作为参数传递给函数。这样我就可以得到这个函数的结果，但是从$ code> generate_series 的每个/每个月的组合。在这一点上我被困住了。

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.

我正在使用PostgreSQL 8.3.14。

I'm using PostgreSQL 8.3.14.

推荐答案

您尝试的工作方式如下：

What you are trying to to could work like this:

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

主要要点：

$ b $

• 你写了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没有被defualt安装。您可能需要在数据库中发出一次 CREATE LANGUAGE plpgsql; 。请参阅此处的手册。



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.

这篇关于PostgreSQL generate_series（）与SQL函数作为参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！