Specify a dblink column definition list from a local existing type

Question

I use dblink to move certain data between databases. Everything saves and sounds, but I wonder if there is a more convenient way to define a list of definition columns for the dblink query result column. I can do something like this:

SELECT * FROM dblink('dbname=remote', 'select * from test') AS t1(id integer, data text); 

The tables I interact with have the same schema definition in both databases (remote and local). I was thinking of something like:

 SELECT * FROM dblink('dbname=remote', 'select * from test') AS t1 LIKE public.test; 

Or:

 SELECT * FROM dblink('dbname=remote', 'select * from test') AS t1::public.test; 

The column definition list tends to become quite long. Is there something I could miss?

EDIT:

Since this was a problem for me, before I created a small function as a workflow.

 CREATE OR REPLACE FUNCTION dblink_star_func(_conn text, _schema_name text, _table_name text) RETURNS text LANGUAGE PLPGSQL VOLATILE STRICT AS $function$ DECLARE _dblink_schema text; _cols text; _q text; _func_name text := format('star_%s', $3); _func text; BEGIN SELECT nspname INTO _dblink_schema FROM pg_namespace n, pg_extension e WHERE e.extname = 'dblink' AND e.extnamespace = n.oid; SELECT array_to_string(array_agg(column_name || ' ' || udt_name), ', ') INTO _cols FROM information_schema.columns WHERE table_schema = $2 AND table_name = $3; _q := format('SELECT * FROM %I.dblink(%L, %L) AS remote (%s)', _dblink_schema, _conn, format('SELECT * FROM %I.%I', $2, $3), _cols ); _func := $_func$ CREATE OR REPLACE FUNCTION %s() RETURNS SETOF %I.%I LANGUAGE SQL VOLATILE STRICT AS $$ %s; $$ $_func$; EXECUTE format(_func, _func_name, $2, $3, _q); RETURN _func_name; END; $function$; 

This function creates and gives a function that completes the dblink call. This, of course, was not intended for heavy lifting, but convenience. It would be nice if this turned out to be completely optional.

 > select dblink_star_func('dbname=ben', 'public', 'test'); ┌──────────────────┐ │ dblink_star_func │ ├──────────────────┤ │ star_test │ └──────────────────┘ (1 row) > select * from star_test() where data = 'success'; ┌────┬─────────┐ │ id │ data │ ├────┼─────────┤ │ 1 │ success │ └────┴─────────┘ (1 row) 

Answer 1

You may need to make sure your types are always in sync, but this should work:

 SELECT (t1::test).* FROM dblink('dbname=remote', 'select * from test') AS t1; 

The key is that you often need parentheses to make sure that the parser knows that you are dealing with tuples.

For example, this works for me:

  CREATE TABLE test (id int, test bool); select (t1::test).* from (select 1, true) t1; 

But this causes a syntax error:

  select t1::test.* from (select 1, true) t1; 

Answer 2

Try something like this:

 select (rec).* from dblink('dbname=...','select myalias from foreign_table myalias') t1 (rec local_type) 

Example (get table statistics from another database):

 select (rec).* from dblink('dbname=foreignDb','select t1 from pg_stat_all_tables t1') t2 (rec pg_stat_all_tables)