PostgreSQL – UUID Array in Stored Procedure Throws Explicit Type Casts Needed

postgresql-10postgresql-9.6stored-proceduresuuid

I have a stored procedure where I have declared an array of UUIDs, on using that variable, it throws casting exception.

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
$$;

The guid is of type UUID, but at this line, I get an error which says:

[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.

Best Answer

Change the assignment operator to := or use 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-9894-11e5-be38-001d42e813fe}} |

db<>fiddle here

Related Question