Return type for function with array_agg()

Question

I'm trying to create a function that returns an array of strings, 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 table 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..."}

Answer 1

A function needs to declare a return type. An array can only be based on a well known element type. An anonymous record is not allowed. So create a composite type that fits your needs (unless there is a table or view already defining the row type).

CREATE TYPE my_type AS (
  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 ( ...)

(A temporary table is dropped at the end of the session, any function building on the the type would be broken after that.)

Use that as base type for the array. You can use a simple SQL function for the purpose:

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;

Call:

SELECT foo();

Simple alternative with text[]

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 SO for the plpgsql tag, you'll find many examples ..

Remember to call a set-returning function with:

SELECT * FROM foo();

Answer 2

Minor optimization based on Erwin's great answer(in the same page):
--create table: create table judgements(id integer, sampleid integer , alterlevel integer , judgements_timestamp timestamp with time zone );

--create type:

create type judgements_type as(
   ts text, alterlevel int, time_Passed interval
);

full function:

create or replace function alarm_event_list
(_sampleid integer,
 starttime timestamp without time zone, 
stoptime timestamp without time zone)
returns judgements_type[] as
$body$
select array_agg(result::judgements_type) from
(select to_char(judgements_timestamp,'YYYY-MM-DD HH24:MI:SS'),
      alterlevel,
     coalesce( judgements_timestamp - lag(judgements_timestamp)
         over (order by judgements_timestamp), make_interval(0,0,0))
from judgements
where  sampleid = _sampleid
and alterlevel > 0
and judgements_timestamp >= starttime
and judgements_timestamp <= stoptime) result;
$body$ language sql;

---

select * from alarm_event_list(11, '2013-09-17 00:00:00', '2013-09-17 23:59:59'); will return one aggregated array.
Make it to jsonb:

select * from to_jsonb(
    alarm_event_list(11, '2013-09-17 00:00:00', '2013-09-17 23:59:59'));

Make it 2 rows with separated json text:

CREATE OR REPLACE FUNCTION public.array_transport(all_items anyarray)
 RETURNS SETOF text
 LANGUAGE plpgsql
 STRICT
AS $function$
declare
  item  record;
begin
foreach   item  in array all_items
loop
   return next(to_json(item)::text);
   end loop;
end;
$function$

then

select * from array_transport
    (alarm_event_list(11, '2013-09-17 00:00:00', '2013-09-17 23:59:59'));