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
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
|
==================================================================
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%';
|
