Return an Array Column from a PostgreSQL Stored Function

Question

I have a function that returns a query based on this answer

It works fine except for the columns that are of type array - result of array_agg on tuples

Simplified Example

Note that in the real case the arrays result from a group by subquery.

 CREATE OR REPLACE FUNCTION get_array()
  RETURNS TABLE (o_id   int 
               , o_arr text[]
               )
  LANGUAGE plpgsql AS
$func$
BEGIN
   RETURN QUERY
   with dt as
    (select 'x' col1, 'y' col2)
   select 1 id, 
       (select array_agg((col1,col2)) from dt) arr; 
END
$func$;

SELECT * FROM get_array();

Error

SQL Error [42804]: ERROR: structure of query does not match function result type
  Detail: Returned type record[] does not match expected type text[] in column 2.
  Where: SQL statement "with dt as

    (select 'x' col1, 'y' col2)

   select 1 id, 

       (select array_agg((col1,col2)) from dt) arr"
PL/pgSQL function get_array() line 3 at RETURN QUERY

How should I define the array column in the returning table type?

The query returns following data

with dt as
(select 'x' col1, 'y' col2)
select 1 id, 
       (select array_agg((col1,col2)) from dt) arr;

|id |arr      |
|---|---------|
|1  |{"(x,y)"}|

Why PL/pgSQL here? A `language sql` function would be more efficient — , Feb 03 '22 at 14:38
Well I simple copy the code from the example I linked @a_horse_with_no_name. Not sure why, but if I only change the language to `sql` leaving all other unchnaged I receive `SQL Error [42601]: ERROR: syntax error at or near "RETURN"` — Marmite Bomber, Feb 03 '22 at 14:49
Well, `array_agg((col1,col2))` produces an array of records (`record[]`) not array text (`text[]`) — , Feb 03 '22 at 15:02
Yes, there is probable too much nesting, if I define the type `record[]` instead of `text[]` compilation fails with `PL/pgSQL functions cannot accept type record[]`. Is there a chance with this approach or do I need to split in two simple arrays? @a_horse_with_no_name — Marmite Bomber, Feb 03 '22 at 15:08

Aman · Answer 1 · 2022-02-05T14:06:00.603

1

Array function used to convert your value into array Array agg function used to group of tyo particular column value aggregate value of group by your particular column

CREATE OR REPLACE FUNCTION get_array()
  RETURNS TABLE (o_id   int 
               , o_arr text[]
               )
  LANGUAGE plpgsql AS
$func$
BEGIN
   RETURN QUERY
   with dt as
    (select 'x' col1, 'y' col2)
   select 1 id, 
   (array_agg(array(col1,col2)))::text[] from DT group by 1
END
$func$;

edited Feb 05 '22 at 14:06

answered Feb 03 '22 at 18:57

Aman

89
4

This works, but it returns an array of *strings* `"(x,y)"` not an array of arrays. – Marmite Bomber Feb 04 '22 at 08:54
with dt as (select 'x' col1, 'y' col2) select 1 id, (array_agg(array(col1,col2)))::text[] from DT group by 1 used this query in your function – Aman Feb 05 '22 at 14:04

Return an Array Column from a PostgreSQL Stored Function

1 Answers1