PostgreSQL + dblink: how to handle multiple fields?

Question

I have this select, that collects data from all local databases that have a table named operation:

select
    db_name, 
    unnest((select *
     from dblink(conn_string, 
        'select array_agg(row(opr.id, opr.descricao))
         from Operacao opr') 
    as (result text array))) as result
from (
    select 
        db_name, 
        table_name,
        conn_string,
        (select table_exists
         from dblink(db.conn_string, 
         '(select Count(*) > 0 from information_schema.tables where table_name = ''' || db.table_name || ''')') as (table_exists Boolean))
    from (
        SELECT 
            datname as db_name,
            'user=postgres password=postgres dbname=' || datname as conn_string,
            'operacao'::text as table_name
        FROM pg_database
        WHERE datistemplate = false
        limit 4
    ) db
) db
where table_exists;

This works somewhat okay, except it is bringing the id and descricao fields as a single column. I tried various variants of the result text array, but couldn't manage to turn the result into a record. Is there any way to split those columns (other than the obvious string manipulation)?

Why are you using `array_agg` in the first place? Per [dblink](https://www.postgresql.org/docs/current/contrib-dblink-function.html) could you not do: `select * from dblink(conn_string, 'select opr.id, opr.descricao from Operacao opr') as t1(id integer, descricao text)`. I am guessing at the types for `id` and `descricao `, modify to actual types if needed. — Adrian Klaver, Dec 29 '22 at 16:24
If you try to do it that way, it will only return the error "subquery must return only one column", because `dblink()` can only return a single value; the `array_agg()` -> `unnest()` combo works around that limitation, because it is returning a single value, that just happens to be an array. Please, see: https://stackoverflow.com/a/72562629/679240 — Haroldo_OK, Dec 29 '22 at 16:42
That is not true, I just tested this on my machine: `select * from dblink('dbname=production user=postgres', 'select cell_per, category from cell_per') as t1(cell_per integer, category varchar);` and it worked just fine. I got two columns for each row. — Adrian Klaver, Dec 29 '22 at 16:52
You're right; by turning the `dblink()` call into a join, it worked; many thanks. — Haroldo_OK, Dec 29 '22 at 17:31

score 0 · Answer 1 · answered Dec 29 '22 at 17:34

As per Adrian Klaver's suggestion, I rewrote the outermost dblink() call into a join:

select
    db_name, t1.*
from (
    select 
        db_name, 
        table_name,
        conn_string,
        (select table_exists
         from dblink(db.conn_string, 
         '(select Count(*) > 0 from information_schema.tables where table_name = ''' || db.table_name || ''')') as (table_exists Boolean))
    from (
        SELECT 
            datname as db_name,
            'user=postgres password=postgres dbname=' || datname as conn_string,
            'operacao'::text as table_name
        FROM pg_database
        WHERE datistemplate = false
        limit 4
    ) db
) db
inner join dblink(conn_string, 'select opr.id, opr.descricao from operacao opr') as t1(id bigint, descricao text) on true
where table_exists;

PostgreSQL + dblink: how to handle multiple fields?

1 Answers1