"cannot assign non-composite value to a record variable "，同时将结果存储在数组中

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数组包含单一类型的值。可以使用int[]所有整数，varchar[]所有字符，依此类推，但不能在单个数组中混合类型。另一方面，复合类型可以做到这一点，包括不同类型的值。在plpgsql中，声明record基本上是声明一个匿名复合类型，该类型获取传递给它的任何实际复合类型的属性。这就是为什么会出现第二个错误"不能将非复合值分配给记录变量"；。您正试图将数组分配给复合类型，在本例中为record。假设要存储的值都是date，则可以省去create type apc_dates_pair ...，只执行l_table1_results date[]。然后使用array循环中显示的形式对数组进行迭代。