主页 / dba / 问题 / 249736
Accepted
Pavanraotk
Asked: 2019-09-27 16:20:10 +0800 CST

在存储过程中声明的 UUID 数组，在访问时抛出需要显式类型转换

  • 772

我有一个存储过程，我在其中声明了一个 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.
stored-procedures postgresql-9.6

1 个回答

  1. Best Answer

    将赋值运算符更改为:=或使用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-9894d42e813fe,672124b6-9894d42e813fe}

    db<>在这里摆弄

    • 1

相关问题