query

PostgreSQL 8.1.4的源码 适用于Linux下的开源数据库系统

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

dblink -- Returns a set from a remote database

Synopsis

dblink(text connstr, text sql [, bool fail_on_error])
dblink(text connname, text sql [, bool fail_on_error])
dblink(text sql [, bool fail_on_error])

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"

  fail_on_error

    If true (default when not present) then an ERROR thrown on the remote side
    of the connection causes an ERROR to also be thrown locally. If false, the
    remote ERROR is locally treated as a NOTICE, and no rows are returned.

Outputs

  Returns setof record

Example usage

select * from dblink('dbname=postgres','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=postgres');
 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=postgres','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%';