Specify dblink column definition list from a local existing type

Question

I am using dblink to move certain data between databases. Everything is save and sound but I am wondering if there is a more convenient way to define the column definition list of a dblink query result. I can do something like this: ``` SELECT * FROM dblink('dbname=remote', 'select * from test') AS t1(id integer, data text); ``` The tables I'm interacting with have the same schema definition in both databases (remote & 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 may have overlooked? EDIT: As this has been a problem for me before I created a small function as a work-around. ``` CREATE OR REPLACE FUNCTION dblink_star_func(_conn text, _schema_name text, _table_name text) RETURNS text LANGUAGE PLPGSQL VOLATILE STRICT AS $function$ DECL…

Accepted Answer

You might need to make sure that 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 often you need parentheses to ensure that the parser knows 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 throws a syntax error:

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

Specify dblink column definition list from a local existing type

Solution

Problem