array_agg（）函数的返回类型 [英] Return type for function with array_agg()

问题描述

我试图创建一个返回字符串数组的函数，我可以在没有函数的情况下执行它，并返回一个记录[] 类型。我试图在它所说的不支持的函数中返回那种类型的结果。

  CREATE OR REPLACE FUNCTION alarmEventList（sampleID integer 
，不带时区的starttime时间戳
，不带时区的stoptime时间戳）
 RETURNS文本[] AS 
 DECLARE 
结果记录[]; 
 BEGIN 
 $ b $ select array_agg（result）
 from（select to_char（Timestamp，'YYYY-MM-DD HH24：MI：SS'），AlertLevel
，时间戳 - 滞后（时间戳）结束（按时间戳排序）
从判断
 WHERESampleID= sampleid和Timestamp> = starttime 
和时间戳<=停止时间）作为结果，其中AlertLevel> 0; 
 
返回结果; 
 END 
 
判决的定义
 ID | SampleID | AlertLevel |时间戳
整数|整数|整数|带时区的时间戳
 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 ...} 
  

解决方案

函数需要声明一个返回类型。数组只能建立在众所周知的基本类型上。匿名记录是不允许的。因此，创建一个适合您需要的组合类型。

  CREATE TYPE my_type（
 ts text 
，alertlevel int 
，time_passed interval 
）; 
  

出于测试目的，您还可以创建临时表来在会话期间注册组合类型：

  CREATE TEMP TABLE my_type（...）
  

温度。在会话结束时删除表，并在该类型上构建任何函数，在此之后将被破坏。

使用它作为数组的基本类型。您可以使用更简单的sql函数来实现您的目的：
$ b $ pre $ CREATE OR REPLACE FUNCTION foo（）
RETURNS my_type [] AS
$ func $
SELECT array_agg（result :: my_type） - 您必须投射记录！
FROM（
SELECT to_char（Timestamp，'YYYY-MM-DD HH24：MI：SS'）
，AlertLevel
，Timestamp - lag（时间戳）OVER（ORDER BY时间戳）
FROM判断
WHERESampleID= sampleid
AND时间戳> =开始时间
AND时间戳< ; =停止时间
）结果
WHEREAlertLevel> 0;
$ func $
LANGUAGE sql;


通话：

  SELECT foo（）; 
  

带文本的简单替代[]

当然你也可以转换为 text / text [] 。你失去了列名和类型信息，但它可以直接使用：

  CREATE OR REPLACE FUNCTION foo（）
 RETURNS text [] AS 
 $ func $ 
 SELECT array_agg（result :: text） - 将记录转换为文本！ 
 FROM（...）结果
 ...; 
 $ func $ 
 LANGUAGE sql; 
  

如果您实际上不需要数组，则可以取消 array_agg（ ），返回单个行并用 RETURNS TABLE（...）声明返回类型。根据 plpgsql 标记在SO上搜索，即可找到很多例子。

请记住调用这样一个函数：

  SELECT * FROM foo（）; 
  

I'm trying to create a function that returns an array of string, I'm able to do it without a function and returns a record[] type, when i try to return that type of result in the function it says that is not supported.

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

Definition of Judgements
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

I was thinking to return a text[] but I don't find the way to make this query a text type or string.

I want to return something like this:

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

解决方案

A function needs to declare a return type. An array can only be built upon a well known base type. An anonymous record is not allowed. So create a composite type that fits your needs.

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

For testing purposes you can also create a temporary table to register a composite type for the duration of a session:

CREATE TEMP TABLE my_type ( ...)

The temp. table is dropped at the end of the session and any function building on the the type, will be broken after that.

Use that as base type for the array. You can use a simpler sql function for your purpose:

CREATE OR REPLACE FUNCTION foo()
  RETURNS my_type[] AS
$func$
SELECT array_agg(result::my_type)  -- you must cast the record!
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;

Call:

SELECT foo();

Simple alternative with text[]

Of course you can also cast to text / text[]. You lose column names and type information, but it works out of the box:

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

If you don't actually need an array, you can scrap array_agg(), return individual rows and declare the return type with RETURNS TABLE (...). Search on SO under the plpgsql tag, you'll find many examples ..

Remember to call such a function with:

SELECT * FROM foo();

这篇关于array_agg（）函数的返回类型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！