PLPGSQL store columns of a 2D query output into array variables

Question

In some step of a plpgsql function I need to store a 2D query result into array variables.

The following code does the job for scalars (but fails with arrays):

SELECT col_a, col_b
FROM my_table
WHERE col_c = condition
INTO var_a, var_b;

The following does the job for ONE column and ONE array variable but not more than that:

SELECT ARRAY(
    SELECT col_a
    FROM my_table
    WHERE col_c > condition
) INTO arr_a;

How could I store multiple rows from col_a, b, c, d... into their respective array variables without having to do a separate query for each column? Like in the first code example but for multiple rows and arrays.

Answer 1

Use array_agg() to build the arrays. Caution, it will aggregate NULL as well, so make sure this is what you want.

dbfiddle with data and example functions.

I am assuming that the predicate on col_c is intended to be the same for both col_a and col_b. You will have to use FILTER or some other construct if they require separate predicates. I put an example of FILTER in the fiddle.

Input table:

CREATE TABLE my_table(col_a int, col_b int, col_c int);
INSERT INTO my_table VALUES
    (1, 11, 21),
    (2, 12, 22),
    (3, 13, 23),
    (4, 14, 24),
    (5, 15, 25),
    (6, 16, 26),
    (7, 17, 27),
    (8, 18, 28),
    (9, 19, 29),
    (10, 20, 30);

Function:

CREATE OR REPLACE FUNCTION array_load() RETURNS RECORD
 LANGUAGE plpgsql
 IMMUTABLE PARALLEL SAFE STRICT
AS $function$
DECLARE
    arr_a integer[];
    arr_b integer[];
BEGIN
SELECT array_agg(col_a), array_agg(col_b)
FROM my_table
WHERE col_c > 26
INTO arr_a, arr_b;
RETURN (arr_a, arr_b);
END;
$function$

Output:

select array_load();
           array_load               
--------------------------------
 ("{7,8,9,10}","{17,18,19,20}")
(1 row)

Reference: https://www.postgresql.org/docs/current/functions-aggregate.html