query

PostgreSQL7.4.6 for Linux

==================================================================
Name

dblink -- Returns a set from a remote database

Synopsis

dblink(text connstr, text sql)
dblink(text connname, text sql)
dblink(text sql)

Inputs

  connname
  connstr
    If two arguments are present, the first is first assumed to be a specific
    connection name to use. If the name is not found, the argument is then
    assumed to be a valid connection string, of standard libpq format,
    e.g.: "hostaddr=127.0.0.1 dbname=mydb user=postgres password=mypasswd"

    If only one argument is used, then the unnamed connection is used.

  sql

    sql statement that you wish to execute on the remote host
    e.g. "select * from pg_class"

Outputs

  Returns setof record

Example usage

select * from dblink('dbname=template1','select proname, prosrc from pg_proc')
 as t1(proname name, prosrc text) where proname like 'bytea%';
  proname   |   prosrc
------------+------------
 byteacat   | byteacat
 byteaeq    | byteaeq
 bytealt    | bytealt
 byteale    | byteale
 byteagt    | byteagt
 byteage    | byteage
 byteane    | byteane
 byteacmp   | byteacmp
 bytealike  | bytealike
 byteanlike | byteanlike
 byteain    | byteain
 byteaout   | byteaout
(12 rows)

select dblink_connect('dbname=template1');
 dblink_connect
----------------
 OK
(1 row)

select * from dblink('select proname, prosrc from pg_proc')
 as t1(proname name, prosrc text) where proname like 'bytea%';
  proname   |   prosrc
------------+------------
 byteacat   | byteacat
 byteaeq    | byteaeq
 bytealt    | bytealt
 byteale    | byteale
 byteagt    | byteagt
 byteage    | byteage
 byteane    | byteane
 byteacmp   | byteacmp
 bytealike  | bytealike
 byteanlike | byteanlike
 byteain    | byteain
 byteaout   | byteaout
(12 rows)

select dblink_connect('myconn','dbname=regression');
 dblink_connect
----------------
 OK
(1 row)

select * from dblink('myconn','select proname, prosrc from pg_proc')
 as t1(proname name, prosrc text) where proname like 'bytea%';
  proname   |   prosrc
------------+------------
 bytearecv  | bytearecv
 byteasend  | byteasend
 byteale    | byteale
 byteagt    | byteagt
 byteage    | byteage
 byteane    | byteane
 byteacmp   | byteacmp
 bytealike  | bytealike
 byteanlike | byteanlike
 byteacat   | byteacat
 byteaeq    | byteaeq
 bytealt    | bytealt
 byteain    | byteain
 byteaout   | byteaout
(14 rows)


==================================================================
A more convenient way to use dblink may be to create a view:

 create view myremote_pg_proc as
 select *
 from dblink('dbname=template1','select proname, prosrc from pg_proc')
 as t1(proname name, prosrc text);

Then you can simply write:

   select * from myremote_pg_proc where proname like 'bytea%';