“无法将非复合值分配给记录变量”将结果存储在数组中时

Question

在 Postgres 13 上，我有一个触发器执行表的 ON UPDATE 。在此触发器中，我想将查询结果存储在数组中，因为我需要在内部迭代器中多次使用它。通过这种方式，我可以避免在每次迭代时执行相同的查询，并且可以重用该数组。

起初我尝试这样，我将变量输入为数组记录：

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;

但是我得到了

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

做一些研究我发现不可能用查询的结果分配内存中的变量，而是我需要使用自定义类型。好的，所以我也尝试了这个

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;

不同的错误，但仍然是一个错误：

cannot assign non-composite value to a record variable 

从中我没有发现太多。

是否有可能在记录数组中存储临时查询，然后迭代它们？

Answer 1

解释多于答案：

您似乎将复合类型与数组混淆了，它们在 Postgres 中是不同的东西。最值得注意的是 Postgres Array 包含单一类型的值。您可以使用 int[] 所有整数，varchar[] 所有字符，依此类推，但不能在单个数组中混合类型。另一方面， 复合类型 可以做到这一点，包含的值属于不同类型。在 plpgsql 中声明一个记录基本上是声明一个匿名复合类型，该类型获取传递给它的任何实际复合类型的属性。这就是你收到第二个错误的原因； “无法将非复合值分配给记录变量”。您正在尝试将数组分配给复合类型，在本例中为记录。鉴于您要存储的值都是date，您可以省略create type apc_dates_pair ...，而只需执行l_table1_results date[] 。然后使用 所示的形式迭代数组数组循环。