function - 查询在 plpgsql 函数中没有目的地？

Question

我有这个函数，但它给出了一个错误:

query has no destination for result data

功能:

CREATE OR REPLACE FUNCTION "alarmEventList"(sampleid integer
                            , starttime timestamp without time zone
                            , stoptime timestamp without time zone)
RETURNS text[] AS
$BODY$BEGIN
select array_agg(result::text) 
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;
return result;
END
$BODY$
LANGUAGE plpgsql VOLATILE

在我尝试运行之后:

CREATE OR REPLACE FUNCTION alarm_event_list(sampleid integer
                           , starttime timestamp without time zone
                           , stoptime timestamp without time zone)
  RETURNS text[] AS $$
 select array_agg(result::text) 
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;
$$ LANGUAGE SQL;

给我一​​个错误，说“sampleid”不存在，即使它只是一个声明参数。我不知道为什么，因为我将所有内容都放在了结果中。

Answer 1

任何嵌入式查询的结果都应保存为 plpgsql 语言的某个结果(或者您必须使用 PERFORM 语句)。

你可以写一个函数:

CREATE OR REPLACE FUNCTION foo(..)
RETURNS text[] AS $$
DECLARE result text[];
BEGIN
  result := (SELECT array_agg(...) FROM sometable);
  RETURN result;
END; $$
LANGUAGE plpgsql;

或

CREATE OR REPLACE FUNCTION foo(..)
RETURNS text[] AS $$
DECLARE result text[];
BEGIN
  SELECT INTO result array_agg(...) FROM sometable;
  RETURN result;
END; $$
LANGUAGE plpgsql;