Search code examples
arrayspostgresqldblink

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


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.


Solution

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