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 — user330315
– user330315, Commented Feb 3, 2022 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
– Marmite Bomber, Commented Feb 3, 2022 at 14:49
Well, array_agg((col1,col2)) produces an array of records (record[]) not array text (text[]) — user330315
– user330315, Commented Feb 3, 2022 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
– Marmite Bomber, Commented Feb 3, 2022 at 15:08

Aman · Accepted Answer · 2022-02-05 14:06:00Z

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 5, 2022 at 14:06

answered Feb 3, 2022 at 18:57

Aman

994 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Marmite Bomber Over a year ago

This works, but it returns an array of strings "(x,y)" not an array of arrays.

Aman Over a year ago

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

Collectives™ on Stack Overflow

Return an Array Column from a PostgreSQL Stored Function

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related