POSTGRESQL: How to store dblink SELECT result to an ARRAY?

Question

Let's say I have a table with a UUID type column, and I want to fetch all the uuids to an array, which will be used in a loop.

I have a PLSQL block as follows (for simplicity, I only print array values):

DO $$
DECLARE
    my_arr UUID ARRAY;
    i UUID;
BEGIN
    --my_arr := '{{40ffb910-aaaa-eeee-dddd-4d53dd14aaaa}, {40ffb910-aaaa-eeee-dddd-4d53dd14bbbb}}';
    my_arr := (SELECT * FROM dblink ('my_conn', 'SELECT uuid_column FROM my_table') AS my_arr(name UUID ARRAY));
    FOREACH i IN ARRAY my_arr
    LOOP
        RAISE NOTICE '%', i;
    END LOOP;
END $$;

This yields an error though:

[22P02] ERROR: malformed array literal: "40ffb910-aaaa-eeee-dddd-4d53dd14aaaa"
Details: Array value must start with "{" or dimension information.

Is it possible to store dblink SELECT result into an array? Postgresql docs do not show any relevant example.

Answer 1

Use array_agg() to create an array:

DO $$
DECLARE
    my_arr UUID ARRAY;
    i UUID;
BEGIN
    --my_arr := '{{40ffb910-aaaa-eeee-dddd-4d53dd14aaaa}, {40ffb910-aaaa-eeee-dddd-4d53dd14bbbb}}';
    my_arr := (SELECT * FROM dblink ('my_conn', 'SELECT array_agg(uuid_column) FROM my_table') AS my_arr(name UUID ARRAY));
    FOREACH i IN ARRAY my_arr
    LOOP
        RAISE NOTICE '%', i;
    END LOOP;
END $$;