Search code examples
postgresqlpostgresql-11

How to load a query into composite type array?

I have a query with 3 columns and multiple rows. I need to store the query result into an array of a composite type. Can someone help how to achieve it using array_agg or a different method instead of using a Loop?

CREATE TYPE type1 AS
(
    id bigint,
    name character varying(4000),
    created_on date
);

do
$$
declare
arr_type1 type1[];
--Query is select st_id, st_name, recorded_dt from st_samples;

end;
$$
language plpgsql;

I need to load the data in the above commented query into the arr_type1 array. Thanks in advance.

Solution

  • This might be what you're looking for:

    do $$
declare 
 t type1[];
begin
 select array_agg(row(id,name,recorded_dt)::type1) into t from st_samples;
 raise notice '%',t;
end;
$$

    Doesn't do anything productive, but t holds the array of type1 and is showned in output.