0

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.

1 Answer 1

1

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

1
  • This worked for me! Thanks a ton :). Also fiddle - new thing, that's so good! Helps me really well!
    – Pavanraotk
    Commented Sep 27, 2019 at 10:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.