sql - array_agg() 函数的返回类型

Question

我正在尝试创建一个返回字符串数组的函数，我可以在没有函数的情况下执行此操作并返回 record[] 类型，当我尝试返回该类型时导致它表示不支持的功能。

CREATE OR REPLACE FUNCTION alarmEventList(sampleid integer
                          , starttime timestamp without time zone
                          , stoptime timestamp without time zone)
  RETURNS text[] AS
$$
DECLARE
    result record[];
BEGIN
    select array_agg(result)
    from (select to_char("Timestamp", 'YYYY-MM-DD HH24:MI:SS'), "AlertLevel"
               , "Timestamp" - lag("Timestamp") over (order by "Timestamp")
            from "Judgements"
          WHERE "SampleID"=sampleid and "Timestamp" >= starttime
          and "Timestamp" <= stoptime) as result where "AlertLevel" >0;
    return result;
END
$$

表判断的定义:

ID      | SampleID | AlertLevel | Timestamp               
integer | integer  | integer    | timestamp with time zone
   1    |    11    |    1       | 2013-09-17 10:36:40
   2    |    11    |    0       | 2013-09-17 10:36:45
   3    |    11    |    2       | 2013-09-17 10:36:51

我想返回一个 text[] 但我没有找到让这个查询成为文本类型或字符串的方法。

我想返回这样的东西:

{"2013-11-21 10:36:40, 1, 10", "etc...etc..."}

Answer 1

一个函数需要声明一个返回类型。数组只能基于众所周知的元素类型。不允许匿名记录。因此，创建一个符合您需求的复合类型(除非已经有一个表或 View 定义了行类型)。

CREATE TYPE my_type AS (
  ts          text
, alertlevel  int
, time_passed interval
);

出于测试目的，您还可以创建一个临时表以在 session 期间注册复合类型:

CREATE TEMP TABLE my_type ( ...)

(临时表在 session 结束时被删除，任何建立在该类型上的函数都将在此之后被破坏。)

使用它作为数组的基本类型。您可以为此目的使用一个简单的 SQL 函数:

CREATE OR REPLACE FUNCTION foo()
  RETURNS my_type[] AS
$func$
SELECT array_agg(result::my_type)  -- you must cast the row type!
FROM  (
   SELECT to_char("Timestamp", 'YYYY-MM-DD HH24:MI:SS')
        , "AlertLevel"
        , "Timestamp" - lag("Timestamp") OVER (ORDER BY "Timestamp")
   FROM   "Judgements"
   WHERE  "SampleID" = sampleid
   AND    "Timestamp" >= starttime
   AND    "Timestamp" <= stoptime
   ) result
WHERE "AlertLevel" > 0;
$func$
LANGUAGE sql;

调用:

SELECT foo();

使用 text[] 的简单替代方案

您还可以转换为 text/text[]。您丢失了列名和类型信息，但它开箱即用:

CREATE OR REPLACE FUNCTION foo()
  RETURNS text[] AS
$func$
SELECT array_agg(result::text)  -- cast the record to text!
FROM  ( ... ) result
...;
$func$
LANGUAGE sql;

如果您实际上不需要数组，您可以放弃 array_agg()，返回单独的行并使用 RETURNS TABLE (...) 声明返回类型.在 SO 中搜索 plpgsql标签，你会发现很多例子..

记住调用一个返回集合的函数:

SELECT * FROM foo();