0

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
$$ ;
DBot
  • 3
  • 5

1 Answers1

1

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.

Belayer
  • 13,578
  • 2
  • 11
  • 22
  • The question was edited by another user after I asked it, so you saw a different version than I was trying to ask. I've updated the question to be simpler. If you answer it, I'll accept your answer (I believe you know how). My motivation for asking is because the official docs don't have an example – DBot Nov 03 '20 at 01:59
  • Sorry I typically check for edits, guess I forgot this time. But I am not sure what to answer. I do see you have changed it. – Belayer Nov 03 '20 at 03:28