Thread: Use Dblink without column defination

Use Dblink without column defination

From
Chirag Karkera
Date:
Hi Team, 

Appreciate your time to look into this. 

To select from another database I try to use dblink or fdw extension of Postgres, like this:

mesods =>CREATE EXTENSION dblink;

CREATE EXTENSION

mesods => CREATE EXTENSION postgres_fdw;

CREATE EXTENSION

mesods=> select dblink_connect('conn_db_link','foreign_server') ;

 dblink_connect

----------------

 OK

(1 row)

 

mesods=> select * from dblink('foreign_server','select * from ods_sch.emp') AS x(a int,b text);

 a |    b

---+---------

 1 | Gohan

 1 | Piccolo

 1 | Tien

(3 rows)


This works fine when I specify which columns I want to select. 


Is there something that postgres has without specifying the column names we can fetch the data from dblink. 


Awaiting your reply.


Thank you.


Regards,

Chirag Karkera

Re: Use Dblink without column defination

From
Chirag Karkera
Date:
Hi Team,

Any update on this?

Thank You.

Regards,
Chirag Karkera 

On Mon, 23 May, 2022, 1:46 pm Chirag Karkera, <chiragkrkr102@gmail.com> wrote:
Hi Team, 

Appreciate your time to look into this. 

To select from another database I try to use dblink or fdw extension of Postgres, like this:

mesods =>CREATE EXTENSION dblink;

CREATE EXTENSION

mesods => CREATE EXTENSION postgres_fdw;

CREATE EXTENSION

mesods=> select dblink_connect('conn_db_link','foreign_server') ;

 dblink_connect

----------------

 OK

(1 row)

 

mesods=> select * from dblink('foreign_server','select * from ods_sch.emp') AS x(a int,b text);

 a |    b

---+---------

 1 | Gohan

 1 | Piccolo

 1 | Tien

(3 rows)


This works fine when I specify which columns I want to select. 


Is there something that postgres has without specifying the column names we can fetch the data from dblink. 


Awaiting your reply.


Thank you.


Regards,

Chirag Karkera

Re: Use Dblink without column defination

From
Peter Eisentraut
Date:
On 23.05.22 10:16, Chirag Karkera wrote:
> mesods=> select * from dblink('foreign_server','select * from 
> ods_sch.emp') AS x(a int,b text);
> 
>   a |    b
> 
> ---+---------
> 
>   1 | Gohan
> 
>   1 | Piccolo
> 
>   1 | Tien
> 
> (3 rows)
> 
> This works fine when I specify which columns I want to select.
> 
> Is there something that postgres has without specifying the column names 
> we can fetch the data from dblink.

Not in dblink.  You could use foreign-data wrappers, which have a 
different interface, which you might like better.