Создать таблицу из нескольких вызовов функции, возвращающей запись

Question

У меня есть функция, которая делает некоторые базовые статистические данные по диапазону данных на основе меток времени начала и окончания:

CREATE OR REPLACE FUNCTION cal(TIMESTAMP, TIMESTAMP, OUT Date_Time timestamp with time zone, OUT avg numeric, OUT stddev numeric, OUT Rstedv_per numeric) 
AS $$
SELECT
    max(datetime) as Date_Time,
    avg(SO2) AS Mean,
    stddev_samp(so2) as STD_DEV,
    stddev_samp(so2)/avg(SO2)*100 as Rstedv_Per
FROM Table43
WHERE datetime > $1 AND datetime < $2;
$$
 LANGUAGE SQL;

Это прекрасно работает с простым одиночным выбором, таким как это:

select * FROM
 cal('2014-08-02 05:29:00', '2014-08-02 05:32:00')

Но теперь у меня возникли проблемы с созданием другой функции или даже оператора select, который может объединять несколько вызовов функции 'cal'. Например, я хочу вернуть таблицу, которая содержит три периода времени. Таким образом, возвращение будет 4 столбца на 3 строки:

'2014-08-02 05:29:00', '2014-08-02 05:32:00'
'2014-08-02 05:35:00', '2014-08-02 05:39:00'
'2014-08-02 05:45:00', '2014-08-02 05:49:00'

Answer 1

Использовать VALUES выражение для предоставления нескольких строк входных дат. Затем...

Для всех версий Postgres

SELECT cal(a, b)
FROM  (
   VALUES 
      ('2014-08-02 05:29'::timestamp, '2014-08-02 05:32'::timestamp)
    , ('2014-08-02 05:35', '2014-08-02 05:39')
    , ('2014-08-02 05:45', '2014-08-02 05:39')
   ) v(a, b);

Вы можете заменить VALUES выражение с фактической таблицей.

Это возвращает целые строки как один столбец (вместо отдельных столбцов). Вы можете разложить на месте с (cal(a, b)).*, Хотя это работает, это неэффективно. Из-за слабости в синтаксическом анализаторе Postgres это может привести к многократной оценке функции. Детальное объяснение:

• Как избежать множественных ошибок функций с помощью синтаксиса (func()).* В запросе SQL?

Вместо этого используйте подзапрос для лучшей производительности:

SELECT (rec).*
FROM  (
    SELECT cal(a, b)
    FROM  (
       VALUES 
          ('2014-08-02 05:29'::timestamp, '2014-08-02 05:32'::timestamp)
        , ('2014-08-02 05:35', '2014-08-02 05:39')
        , ('2014-08-02 05:45', '2014-08-02 05:39')
       ) v(a, b)
   ) sub;

SQL Fiddle (для pg 8.3, чтобы продемонстрировать, что он работает в старых версиях).

Так как функции, возвращающие множество в списке SELECT, недовольны некоторыми и являются нестандартным SQL.

Postgres 9.3+

Это основная причина, по которой появился Postgres 9.3 (соответствует стандарту SQL) LATERAL JOIN:

SELECT f.*
FROM  (
   VALUES 
      ('2014-08-02 05:29'::timestamp, '2014-08-02 05:32'::timestamp)
    , ('2014-08-02 05:35', '2014-08-02 05:39')
    , ('2014-08-02 05:45', '2014-08-02 05:39')
   ) v(a,b)
  , cal(v.a, v.b) f;

LATERAL JOIN здесь подразумевается, поскольку второй элемент в списке FROM явно ссылается на предыдущую таблицу. Подробности:

• Найти наиболее распространенные элементы в массиве с группой по

SQL Fiddle для текущих Postgres 9.3.

Answer 2

Если ваши даты являются параметрами, вам нужно что-то вроде:

select * 
FROM cal('2014-08-02 05:29:00', '2014-08-02 05:32:00')
union all
select * 
FROM cal('2014-08-02 05:35:00', '2014-08-02 05:39:00')
union all
select * 
FROM cal('2014-08-02 05:45:00', '2014-08-02 05:39:00')

PostgreSQL 9.0+

select cal(a, b)
from 
    values( 
    ('2014-08-02 05:29:00', '2014-08-02 05:32:00'),
    ('2014-08-02 05:29:00', '2014-08-02 05:32:00'),
    ('2014-08-02 05:29:00', '2014-08-02 05:32:00')
    ) as dates (a, b)

Если ваши даты взяты из таблицы и вы работаете с Postresql 9.3:

select cal.*
FROM 
  (select '2014-08-02 05:29:00' a, '2014-08-02 05:32:00' b
      from foo_table) as dates
left join lateral cal(dates.a, dates.b) cal on true