blob: 525ffab45a27ad2f6b5bfc00f8be096631ccef7d (
plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
|
==================================================================
Name
dblink -- Returns a set from a remote database
Synopsis
dblink(text connstr, text sql)
- or -
dblink(text sql)
Inputs
connstr
standard libpq format connection string,
e.g. "hostaddr=127.0.0.1 port=5432 dbname=mydb user=postgres password=mypasswd"
If the second form is used, then the dblink_connect(text connstr) must be
executed first.
sql
sql statement that you wish to execute on the remote host
e.g. "select * from pg_class"
Outputs
Returns setof record
Example usage
test=# 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)
test=# select dblink_connect('dbname=template1');
dblink_connect
----------------
OK
(1 row)
test=# 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)
==================================================================
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%';
|
