在儲存過程中聲明的 UUID 數組，訪問時拋出需要的顯式類型轉換

我有一個儲存過程，我在其中聲明了一個 UUID 數組，在使用該變數時，它會引發強制轉換異常。

create or replace function test.test_function(max integer, interval interval) returns SETOF test.test_table
   language plpgsql
as
$$
DECLARE
   min TIMESTAMP;
   max TIMESTAMP;
   array_of_guids uuid[];
BEGIN
   array_of_guids = ARRAY(SELECT guid FROM test.test_table WHERE guid NOT IN (SELECT guid FROM test.ignore_test));
   ...

END
$$;

guid 的類型為 UUID，但在這一行，我收到一條錯誤消息：

[2019-09-27 12:12:48] Where: PL/pgSQL function test.test_function(integer,interval) line 10 at assignment```

Unable to figure out the syntax to fix the above problem.

將賦值運算符更改為:=或使用select into

create table t (id uuid);
insert into t values 
('672124b6-9894-11e5-be38-001d42e813fe'::uuid),
('672124b6-9894-11e5-be38-001d42e813fe'::uuid),
('672124b6-9894-11e5-be38-001d42e813fe'::uuid),
('672124b6-9894-11e5-be38-001d42e813fe'::uuid);

create or replace function test_array2()
returns uuid[] as
$$
declare
   ar uuid[];
begin
   select array_agg(id) from t into ar;
   return ar;
end
$$
language plpgsql;

select * from test_array2();

create or replace function test_array3()
returns uuid[] as
$$
declare
   ar uuid[];
begin
   ar := array(select array_agg(id) from t);
   return ar;
end
$$
language plpgsql;

select * from test_array3();

✓

| test_array3 |
| :------------------------------------------------------------------------------------------------------------------------------------------------------ |
| {{672124b6-9894-11e5-be38-001d42e813fe,672124b6-9894-11e5-be38-001d42e813fe,672124b6-9894-11e5-be38-001d42e813fe,672124b6-9804-112edfe-3fe,672124b6-9894-112edfe-3}-1

db<>在這裡擺弄

引用自：https://dba.stackexchange.com/questions/249736