~ubuntu-branches/ubuntu/oneiric/postgresql-9.1/oneiric-security

arguments are given, the first one is first looked up as a persistent connection\(aqs name; if found, the command is executed on that connection\&. If not found, the first argument is treated as a connection info string as for

\fBdblink_connect\fR, and the indicated connection is made just for the duration of this command\&.

.SH "ARGUMENTS"

.PP

\fIconname\fR

.RS 4

Name of the connection to use; omit this parameter to use the unnamed connection\&.

.RE

.PP

\fIconnstr\fR

.RS 4

A connection info string, as previously described for

\fBdblink_connect\fR\&.

.RE

.PP

\fIsql\fR

.RS 4

The SQL query that you wish to execute in the remote database, for example

select * from foo\&.

.RE

.PP

\fIfail_on_error\fR

.RS 4

If true (the default when omitted) 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 reported as a NOTICE, and the function returns no rows\&.

.RE

.SH "RETURN VALUE"

.PP

The function returns the row(s) produced by the query\&. Since

\fBdblink\fR

can be used with any query, it is declared to return

record, rather than specifying any particular set of columns\&. This means that you must specify the expected set of columns in the calling query \(em otherwise

PostgreSQL

would not know what to expect\&. Here is an example:

.sp

.if n \{\

.RS 4

.\}

.nf

SELECT *

FROM dblink(\(aqdbname=mydb\(aq, \(aqselect proname, prosrc from pg_proc\(aq)

AS t1(proname name, prosrc text)

WHERE proname LIKE \(aqbytea%\(aq;

.fi

.if n \{\

.RE

.\}

.sp

The

\(lqalias\(rq

part of the

FROM

clause must specify the column names and types that the function will return\&. (Specifying column names in an alias is actually standard SQL syntax, but specifying column types is a

PostgreSQL

extension\&.) This allows the system to understand what

should expand to, and what

proname

in the

WHERE

clause refers to, in advance of trying to execute the function\&. At run time, an error will be thrown if the actual query result from the remote database does not have the same number of columns shown in the

FROM

clause\&. The column names need not match, however, and

100

\fBdblink\fR

101

does not insist on exact type matches either\&. It will succeed so long as the returned data strings are valid input for the column type declared in the

102

FROM

103

clause\&.

104

.SH "NOTES"

105

.PP

106

\fBdblink\fR

107

fetches the entire remote query result before returning any of it to the local system\&. If the query is expected to return a large number of rows, it\(aqs better to open it as a cursor with

108

\fBdblink_open\fR

109

and then fetch a manageable number of rows at a time\&.

110

.PP

111

A convenient way to use

112

\fBdblink\fR

113

with predetermined queries is to create a view\&. This allows the column type information to be buried in the view, instead of having to spell it out in every query\&. For example,

114

.sp

115

.if n \{\

116

.RS 4

117

.\}

118

.nf

119

CREATE VIEW myremote_pg_proc AS

120

SELECT *

121

FROM dblink(\(aqdbname=postgres\(aq, \(aqselect proname, prosrc from pg_proc\(aq)

122

AS t1(proname name, prosrc text);

123

124

SELECT * FROM myremote_pg_proc WHERE proname LIKE \(aqbytea%\(aq;

125

.fi

126

.if n \{\

127

.RE

128

.\}

129

.SH "EXAMPLE"

130

.sp

131

.if n \{\

132

.RS 4

133

.\}

134

.nf

135

SELECT * FROM dblink(\(aqdbname=postgres\(aq, \(aqselect proname, prosrc from pg_proc\(aq)

136

AS t1(proname name, prosrc text) WHERE proname LIKE \(aqbytea%\(aq;

137

proname | prosrc

138

\-\-\-\-\-\-\-\-\-\-\-\-+\-\-\-\-\-\-\-\-\-\-\-\-

139

byteacat | byteacat

140

byteaeq | byteaeq

141

bytealt | bytealt

142

byteale | byteale

143

byteagt | byteagt

144

byteage | byteage

145

byteane | byteane

146

byteacmp | byteacmp

147

bytealike | bytealike

148

byteanlike | byteanlike

149

byteain | byteain

150

byteaout | byteaout

151

(12 rows)

152

153

SELECT dblink_connect(\(aqdbname=postgres\(aq);

154

dblink_connect

155

\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-

156

157

(1 row)

158

159

SELECT * FROM dblink(\(aqselect proname, prosrc from pg_proc\(aq)

160

AS t1(proname name, prosrc text) WHERE proname LIKE \(aqbytea%\(aq;

161

proname | prosrc

162

\-\-\-\-\-\-\-\-\-\-\-\-+\-\-\-\-\-\-\-\-\-\-\-\-

163

byteacat | byteacat

164

byteaeq | byteaeq

165

bytealt | bytealt

166

byteale | byteale

167

byteagt | byteagt

168

byteage | byteage

169

byteane | byteane

170

byteacmp | byteacmp

171

bytealike | bytealike

172

byteanlike | byteanlike

173

byteain | byteain

174

byteaout | byteaout

175

(12 rows)

176

177

SELECT dblink_connect(\(aqmyconn\(aq, \(aqdbname=regression\(aq);

178

dblink_connect

179

\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-

180

181

(1 row)

182

183

SELECT * FROM dblink(\(aqmyconn\(aq, \(aqselect proname, prosrc from pg_proc\(aq)

184

AS t1(proname name, prosrc text) WHERE proname LIKE \(aqbytea%\(aq;

185

proname | prosrc

186

\-\-\-\-\-\-\-\-\-\-\-\-+\-\-\-\-\-\-\-\-\-\-\-\-

187

bytearecv | bytearecv

188

byteasend | byteasend

189

byteale | byteale

190

byteagt | byteagt

191

byteage | byteage

192

byteane | byteane

193

byteacmp | byteacmp

194

bytealike | bytealike

195

byteanlike | byteanlike

196

byteacat | byteacat

197

byteaeq | byteaeq

198

bytealt | bytealt

199

byteain | byteain

200

byteaout | byteaout

201

(14 rows)

202

.fi

203

.if n \{\

204

.RE

205

.\}

Older »