通过多次调用函数创建表以返回记录

Jeff 发表于 Dev

杰夫

我有一个函数，可以根据开始和停止时间戳对一系列数据进行一些基本统计：

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'

欧文·布兰德斯特

使用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解析器中的弱点，这将导致对该函数进行多次评估。详细说明：

如何在SQL查询中使用（func（））。*语法避免使用多个函数评估？

相反，请使用子查询以获得更好的性能：

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是隐含在这里，因为从列表参考上表中明确的第二个项目。细节：