Possible to use a PostgreSQL TYPE to define a dblink table?

Question

In Postgres, you can link to your other databases using dblink like so:

SELECT *
FROM dblink (
    'dbname=name port=1234 host=host user=user password=password',
    'select * from table'
) AS users([insert each column name and its type here]);

But this is quite verbose.

I've shortened it up by using dblink_connect and dblink_disconnect to abstract the connection string from my dblink queries. However, that still leaves me with the manual table definition (i.e., [insert each column name and its type here]).

Instead of defining the table manually, is there a way I can define it with a TYPE or anything else that'd be re-usable?

In my case, the number of remote tables I have to join and the number of columns involved makes my query massive.

I tried something along the lines of:

SELECT *
FROM dblink (
    'myconn',
    'select * from table'
) AS users(postgres_pre_defined_type_here);

But I received the following error:

ERROR: a column definition list is required for functions returning "record"

Answer 1

As you considered creating several types for dblink, you can accept creating several functions as well. The functions will be well defined and very easy to use.

Example:

create or replace function dblink_tables()
returns table (table_schema text, table_name text)
language plpgsql
as $$
begin
    return query select * from dblink (
        'dbname=test password=mypassword', 
        'select table_schema, table_name from information_schema.tables')
    as tables (table_schema text, table_name text);
end $$;

select table_name 
from dblink_tables()
where table_schema = 'public'
order by 1