PostgreSQL generate_series() с функцией SQL в качестве аргументов

Question

У меня есть функция SQL называется get_forecast_history(integer,integer) это требует двух аргументов, месяц и год. Функция возвращает 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):

  товар актуален
---- - -------- ------
2011 10 Продукт1  29
2011 10 Продукт2 10
2011 10 Продукт3  8
2011 10 Продукт4  0
2011 10 Продукт5  2

и т. д. (всего около 30 продуктов). Это история за данный месяц.

У меня также есть другой запрос, который генерирует серию месяцев:

SELECT to_char(DATE '2008-01-01'
            + (interval '1 month' * generate_series(0,57)), 'YYYY-MM-DD') AS 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.

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; один раз в вашей базе данных. Смотрите руководство здесь.

Возможно, вы могли бы упростить две ваши функции в один запрос или функцию, если хотите.