Mano
Mano

Reputation: 711

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.

Upvotes: 0

Views: 621

Answers (1)

Bjarni Ragnarsson
Bjarni Ragnarsson

Reputation: 1781

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.

Upvotes: 1

Related Questions