Блог

У меня вызывается функция 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 $
 

Вызов:

 SELECT * FROM get_forecast_history(10, 2011);
 

Например, создается следующая таблица (тип результата функции — таблица, т.Е. SETOF ):

 у меня фактический продукт
---- -- -------- ------
2011 10 Продукт 1 29
2011 10 Продукт 2 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;
 

Вызов:

 SELECT * FROM f_products_per_month();
 

Основные моменты:

• Окончательное редактирование, чтобы включить пустую строку в течение нескольких месяцев без продуктов.
• Вы написали «ЛЕВОЕ СОЕДИНЕНИЕ», но это не так, как это может работать.
• Есть несколько способов сделать это, но 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; один раз в вашей базе данных. Смотрите руководство здесь.

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