从对返回记录的函数的多次调用中创建表

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

但是现在我在创建另一个函数时遇到了麻烦，甚至是一个可以组合多次调用'cal'函数的select语句。例如，我想返回一个包含三个时间段的表。因此，返回值将是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列表中的set返回函数不受某些人的欢迎，并且是非标准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