"cannot assign non-composite value to a record variable " while storing result in array

Question

on Postgres 13 I have a trigger execute ON UPDATE of a table. In this trigger I want to store in an array a result from a query, because I will need to use it inside an inner iterator, multiple times. In this way I avoid performing the same query at every iteration and I can reuse the array.

At first I tried like this, I typed the variable as array record:

create or REPLACE FUNCTION check_companyprofile_between_records_constraints_trigger_hook()
RETURNS trigger as
$body$
declare
    l_table1_results record[];
    l_table1_result record;

    l_record record;
begin
    l_table1_results := ARRAY(
        select
            date_start, 
            coalesce(date_end, 'infinity'::date) as date_end
        FROM application.section_profile_company_metadata 
        WHERE persona_id = NEW.persona_id and id != new.id 
    );

    for l_record in (
        select foo,bar,baz
        from table2
    )
    loop
    
        foreach l_table1_result in array l_table1_results
        loop
            -- Check some stuffs
        end loop;
    
    end loop;
    
    RETURN NEW;
end
$body$
LANGUAGE plpgsql;

But I'm getting a

Errore SQL [0A000]: ERROR: variable "l_table1_results" has pseudo-type record[]

Doing some research I discovered it is not possible to assign an in-memory variable with the result of a query, but instead I need to use a custom type. Ok, so I tried also this

create type apc_dates_pair as (
    date_start date, 
    date_end date
);

create or REPLACE FUNCTION check_companyprofile_between_records_constraints_trigger_hook()
RETURNS trigger as
$body$
declare
    l_table1_results apc_dates_pair[];
    l_table1_result record;

    l_record record;
begin
    l_table1_results := ARRAY(
        select
            date_start, 
            coalesce(date_end, 'infinity'::date) as date_end
        FROM application.section_profile_company_metadata 
        WHERE persona_id = NEW.persona_id and id != new.id 
    );

    -- TODO do some stuffs with array

    for l_record in (
        select foo,bar,baz
        from table2
    )
    loop
    
        foreach l_table1_result in l_table1_results
        loop
            -- Check some stuffs
        end loop;
    
    end loop;
    
    RETURN NEW;
end
$body$
LANGUAGE plpgsql;

create or REPLACE FUNCTION check_companyprofile_between_records_constraints_trigger_hook()
RETURNS trigger as
$body$
declare
    l_table1_results apc_dates_pair[];
    l_table1_result record;

    l_record record;
begin
    l_table1_results := ARRAY(
        select row(
            date_start, 
            coalesce(date_end, 'infinity'::date)
        )
        FROM application.section_profile_company_metadata 
        WHERE persona_id = NEW.persona_id and id != new.id 
    );

for l_record in (
        select foo,bar,baz
        from table2
    )
    loop
    
        foreach l_table1_result in array l_table1_results
        loop
            -- Check some stuffs
        end loop;
    
    end loop;
    
    RETURN NEW;
end
$body$
LANGUAGE plpgsql;

Different error, but still an error:

cannot assign non-composite value to a record variable 

And from this I did not find much.

Is it possible at all to store a temp query inside and array of records and then iterate them?

Answer 1

An explanation more then an answer:

You seem to be confusing composite types with arrays, they are different things in Postgres. Most notably a Postgres Array contains values of a single type. You can have int[] all integers, varchar[] all characters, and so on, but you cannot mix types in a single array. A Composite Type on the other hand can do just that, have included values that are of different types. In plpgsql declaring a record is to basically declare an anonymous composite type that acquires the properties of whatever actual composite type is passed to it. This is why you get your second error; "cannot assign non-composite value to a record variable". You are trying to assign an array to a composite type, in this case a record. Given that the values you want to store are both dates you could dispense with create type apc_dates_pair ... and just do l_table1_results date[]. Then iterate over the array using the form shown at Array loop.