CODEWITHSUNDEEP
postgresqlplpgsqlpostgresql-13
DBot
DBot

Reputation: 23

How to select an array of composite types into local variable in Postgres PL/pgSQL?

This is a simple question: In PL/pgSQL, how do I select an array of composite objects into a local variable?

I'm on Postgres 13. Here is an example of what I'd like to do:

create type udt_foo as (
    col1 numeric,
    col2 numeric
);

create or replace procedure bar ()
language plpgsql as
$$
declare
    lv_foos              udt_foo[];
begin

    select ...
    into strict lv_foos
    from some_table t;

end
$$ ;

Upvotes: 0

Views: 230

Answers (1)

Belayer
Belayer

Reputation: 14886

Both routines are short so test them, see what the difference between them is, if any. But I would modify both to actually produce output. or to make the comparisons the uniform a 3rd routine that generates the output for both:

create or replace procedure show_bar(udt_foo[]) 
language plpgsql as
$$
declare
    indx integer; 
begin
    for indx in 1 .. array_length(udt_doo,1)
    loop
        raise notice 'foo(col1, col2)=>(%,%)',udt_foo[indx].col1,udt_foo[indx].col2;
    end loop;
end;
$$ ;

Then add the following to each after the select;

call show_bar(lv_foos);

In short, when confronted by 2 or more potentially equivalent methods, test them in your own environment, do not just ask for opinions. Pick the one that produces the desired output. If both do so pick the simplest and most easily understood.

Upvotes: 1

Related Questions