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
$$ ;
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.
