← Back to branch summary

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

« back to all changes in this revision

Viewing changes to contrib/dblink/sql/dblink.sql

  • Committer: Bazaar Package Importer
  • Author(s): Martin Pitt
  • Date: 2011-05-11 10:41:53 UTC
  • Revision ID: james.westby@ubuntu.com-20110511104153-psbh2o58553fv1m0
Tags: upstream-9.1~beta1
Import upstream version 9.1~beta1

Show diffs side-by-side

added added

removed removed

Lines of Context:
contrib/dblink/sql/dblink.sql
 
1
CREATE EXTENSION dblink;
 
2
 
 
3
CREATE TABLE foo(f1 int, f2 text, f3 text[], primary key (f1,f2));
 
4
INSERT INTO foo VALUES (0,'a','{"a0","b0","c0"}');
 
5
INSERT INTO foo VALUES (1,'b','{"a1","b1","c1"}');
 
6
INSERT INTO foo VALUES (2,'c','{"a2","b2","c2"}');
 
7
INSERT INTO foo VALUES (3,'d','{"a3","b3","c3"}');
 
8
INSERT INTO foo VALUES (4,'e','{"a4","b4","c4"}');
 
9
INSERT INTO foo VALUES (5,'f','{"a5","b5","c5"}');
 
10
INSERT INTO foo VALUES (6,'g','{"a6","b6","c6"}');
 
11
INSERT INTO foo VALUES (7,'h','{"a7","b7","c7"}');
 
12
INSERT INTO foo VALUES (8,'i','{"a8","b8","c8"}');
 
13
INSERT INTO foo VALUES (9,'j','{"a9","b9","c9"}');
 
14
 
 
15
-- misc utilities
 
16
 
 
17
-- list the primary key fields
 
18
SELECT *
 
19
FROM dblink_get_pkey('foo');
 
20
 
 
21
-- build an insert statement based on a local tuple,
 
22
-- replacing the primary key values with new ones
 
23
SELECT dblink_build_sql_insert('foo','1 2',2,'{"0", "a"}','{"99", "xyz"}');
 
24
-- too many pk fields, should fail
 
25
SELECT dblink_build_sql_insert('foo','1 2 3 4',4,'{"0", "a", "{a0,b0,c0}"}','{"99", "xyz", "{za0,zb0,zc0}"}');
 
26
 
 
27
-- build an update statement based on a local tuple,
 
28
-- replacing the primary key values with new ones
 
29
SELECT dblink_build_sql_update('foo','1 2',2,'{"0", "a"}','{"99", "xyz"}');
 
30
-- too many pk fields, should fail
 
31
SELECT dblink_build_sql_update('foo','1 2 3 4',4,'{"0", "a", "{a0,b0,c0}"}','{"99", "xyz", "{za0,zb0,zc0}"}');
 
32
 
 
33
-- build a delete statement based on a local tuple,
 
34
SELECT dblink_build_sql_delete('foo','1 2',2,'{"0", "a"}');
 
35
-- too many pk fields, should fail
 
36
SELECT dblink_build_sql_delete('foo','1 2 3 4',4,'{"0", "a", "{a0,b0,c0}"}');
 
37
 
 
38
-- retest using a quoted and schema qualified table
 
39
CREATE SCHEMA "MySchema";
 
40
CREATE TABLE "MySchema"."Foo"(f1 int, f2 text, f3 text[], primary key (f1,f2));
 
41
INSERT INTO "MySchema"."Foo" VALUES (0,'a','{"a0","b0","c0"}');
 
42
 
 
43
-- list the primary key fields
 
44
SELECT *
 
45
FROM dblink_get_pkey('"MySchema"."Foo"');
 
46
 
 
47
-- build an insert statement based on a local tuple,
 
48
-- replacing the primary key values with new ones
 
49
SELECT dblink_build_sql_insert('"MySchema"."Foo"','1 2',2,'{"0", "a"}','{"99", "xyz"}');
 
50
 
 
51
-- build an update statement based on a local tuple,
 
52
-- replacing the primary key values with new ones
 
53
SELECT dblink_build_sql_update('"MySchema"."Foo"','1 2',2,'{"0", "a"}','{"99", "xyz"}');
 
54
 
 
55
-- build a delete statement based on a local tuple,
 
56
SELECT dblink_build_sql_delete('"MySchema"."Foo"','1 2',2,'{"0", "a"}');
 
57
 
 
58
-- regular old dblink
 
59
SELECT *
 
60
FROM dblink('dbname=contrib_regression','SELECT * FROM foo') AS t(a int, b text, c text[])
 
61
WHERE t.a > 7;
 
62
 
 
63
-- should generate "connection not available" error
 
64
SELECT *
 
65
FROM dblink('SELECT * FROM foo') AS t(a int, b text, c text[])
 
66
WHERE t.a > 7;
 
67
 
 
68
-- create a persistent connection
 
69
SELECT dblink_connect('dbname=contrib_regression');
 
70
 
 
71
-- use the persistent connection
 
72
SELECT *
 
73
FROM dblink('SELECT * FROM foo') AS t(a int, b text, c text[])
 
74
WHERE t.a > 7;
 
75
 
 
76
-- open a cursor with bad SQL and fail_on_error set to false
 
77
SELECT dblink_open('rmt_foo_cursor','SELECT * FROM foobar',false);
 
78
 
 
79
-- reset remote transaction state
 
80
SELECT dblink_exec('ABORT');
 
81
 
 
82
-- open a cursor
 
83
SELECT dblink_open('rmt_foo_cursor','SELECT * FROM foo');
 
84
 
 
85
-- close the cursor
 
86
SELECT dblink_close('rmt_foo_cursor',false);
 
87
 
 
88
-- open the cursor again
 
89
SELECT dblink_open('rmt_foo_cursor','SELECT * FROM foo');
 
90
 
 
91
-- fetch some data
 
92
SELECT *
 
93
FROM dblink_fetch('rmt_foo_cursor',4) AS t(a int, b text, c text[]);
 
94
 
 
95
SELECT *
 
96
FROM dblink_fetch('rmt_foo_cursor',4) AS t(a int, b text, c text[]);
 
97
 
 
98
-- this one only finds two rows left
 
99
SELECT *
 
100
FROM dblink_fetch('rmt_foo_cursor',4) AS t(a int, b text, c text[]);
 
101
 
 
102
-- intentionally botch a fetch
 
103
SELECT *
 
104
FROM dblink_fetch('rmt_foobar_cursor',4,false) AS t(a int, b text, c text[]);
 
105
 
 
106
-- reset remote transaction state
 
107
SELECT dblink_exec('ABORT');
 
108
 
 
109
-- close the wrong cursor
 
110
SELECT dblink_close('rmt_foobar_cursor',false);
 
111
 
 
112
-- should generate 'cursor "rmt_foo_cursor" not found' error
 
113
SELECT *
 
114
FROM dblink_fetch('rmt_foo_cursor',4) AS t(a int, b text, c text[]);
 
115
 
 
116
-- this time, 'cursor "rmt_foo_cursor" not found' as a notice
 
117
SELECT *
 
118
FROM dblink_fetch('rmt_foo_cursor',4,false) AS t(a int, b text, c text[]);
 
119
 
 
120
-- close the persistent connection
 
121
SELECT dblink_disconnect();
 
122
 
 
123
-- should generate "connection not available" error
 
124
SELECT *
 
125
FROM dblink('SELECT * FROM foo') AS t(a int, b text, c text[])
 
126
WHERE t.a > 7;
 
127
 
 
128
-- put more data into our slave table, first using arbitrary connection syntax
 
129
-- but truncate the actual return value so we can use diff to check for success
 
130
SELECT substr(dblink_exec('dbname=contrib_regression','INSERT INTO foo VALUES(10,''k'',''{"a10","b10","c10"}'')'),1,6);
 
131
 
 
132
-- create a persistent connection
 
133
SELECT dblink_connect('dbname=contrib_regression');
 
134
 
 
135
-- put more data into our slave table, using persistent connection syntax
 
136
-- but truncate the actual return value so we can use diff to check for success
 
137
SELECT substr(dblink_exec('INSERT INTO foo VALUES(11,''l'',''{"a11","b11","c11"}'')'),1,6);
 
138
 
 
139
-- let's see it
 
140
SELECT *
 
141
FROM dblink('SELECT * FROM foo') AS t(a int, b text, c text[]);
 
142
 
 
143
-- bad remote select
 
144
SELECT *
 
145
FROM dblink('SELECT * FROM foobar',false) AS t(a int, b text, c text[]);
 
146
 
 
147
-- change some data
 
148
SELECT dblink_exec('UPDATE foo SET f3[2] = ''b99'' WHERE f1 = 11');
 
149
 
 
150
-- let's see it
 
151
SELECT *
 
152
FROM dblink('SELECT * FROM foo') AS t(a int, b text, c text[])
 
153
WHERE a = 11;
 
154
 
 
155
-- botch a change to some other data
 
156
SELECT dblink_exec('UPDATE foobar SET f3[2] = ''b99'' WHERE f1 = 11',false);
 
157
 
 
158
-- delete some data
 
159
SELECT dblink_exec('DELETE FROM foo WHERE f1 = 11');
 
160
 
 
161
-- let's see it
 
162
SELECT *
 
163
FROM dblink('SELECT * FROM foo') AS t(a int, b text, c text[])
 
164
WHERE a = 11;
 
165
 
 
166
-- close the persistent connection
 
167
SELECT dblink_disconnect();
 
168
 
 
169
--
 
170
-- tests for the new named persistent connection syntax
 
171
--
 
172
 
 
173
-- should generate "missing "=" after "myconn" in connection info string" error
 
174
SELECT *
 
175
FROM dblink('myconn','SELECT * FROM foo') AS t(a int, b text, c text[])
 
176
WHERE t.a > 7;
 
177
 
 
178
-- create a named persistent connection
 
179
SELECT dblink_connect('myconn','dbname=contrib_regression');
 
180
 
 
181
-- use the named persistent connection
 
182
SELECT *
 
183
FROM dblink('myconn','SELECT * FROM foo') AS t(a int, b text, c text[])
 
184
WHERE t.a > 7;
 
185
 
 
186
-- use the named persistent connection, but get it wrong
 
187
SELECT *
 
188
FROM dblink('myconn','SELECT * FROM foobar',false) AS t(a int, b text, c text[])
 
189
WHERE t.a > 7;
 
190
 
 
191
-- create a second named persistent connection
 
192
-- should error with "duplicate connection name"
 
193
SELECT dblink_connect('myconn','dbname=contrib_regression');
 
194
 
 
195
-- create a second named persistent connection with a new name
 
196
SELECT dblink_connect('myconn2','dbname=contrib_regression');
 
197
 
 
198
-- use the second named persistent connection
 
199
SELECT *
 
200
FROM dblink('myconn2','SELECT * FROM foo') AS t(a int, b text, c text[])
 
201
WHERE t.a > 7;
 
202
 
 
203
-- close the second named persistent connection
 
204
SELECT dblink_disconnect('myconn2');
 
205
 
 
206
-- open a cursor incorrectly
 
207
SELECT dblink_open('myconn','rmt_foo_cursor','SELECT * FROM foobar',false);
 
208
 
 
209
-- reset remote transaction state
 
210
SELECT dblink_exec('myconn','ABORT');
 
211
 
 
212
-- test opening cursor in a transaction
 
213
SELECT dblink_exec('myconn','BEGIN');
 
214
 
 
215
-- an open transaction will prevent dblink_open() from opening its own
 
216
SELECT dblink_open('myconn','rmt_foo_cursor','SELECT * FROM foo');
 
217
 
 
218
-- this should not commit the transaction because the client opened it
 
219
SELECT dblink_close('myconn','rmt_foo_cursor');
 
220
 
 
221
-- this should succeed because we have an open transaction
 
222
SELECT dblink_exec('myconn','DECLARE xact_test CURSOR FOR SELECT * FROM foo');
 
223
 
 
224
-- commit remote transaction
 
225
SELECT dblink_exec('myconn','COMMIT');
 
226
 
 
227
-- test automatic transactions for multiple cursor opens
 
228
SELECT dblink_open('myconn','rmt_foo_cursor','SELECT * FROM foo');
 
229
 
 
230
-- the second cursor
 
231
SELECT dblink_open('myconn','rmt_foo_cursor2','SELECT * FROM foo');
 
232
 
 
233
-- this should not commit the transaction
 
234
SELECT dblink_close('myconn','rmt_foo_cursor2');
 
235
 
 
236
-- this should succeed because we have an open transaction
 
237
SELECT dblink_exec('myconn','DECLARE xact_test CURSOR FOR SELECT * FROM foo');
 
238
 
 
239
-- this should commit the transaction
 
240
SELECT dblink_close('myconn','rmt_foo_cursor');
 
241
 
 
242
-- this should fail because there is no open transaction
 
243
SELECT dblink_exec('myconn','DECLARE xact_test CURSOR FOR SELECT * FROM foo');
 
244
 
 
245
-- reset remote transaction state
 
246
SELECT dblink_exec('myconn','ABORT');
 
247
 
 
248
-- open a cursor
 
249
SELECT dblink_open('myconn','rmt_foo_cursor','SELECT * FROM foo');
 
250
 
 
251
-- fetch some data
 
252
SELECT *
 
253
FROM dblink_fetch('myconn','rmt_foo_cursor',4) AS t(a int, b text, c text[]);
 
254
 
 
255
SELECT *
 
256
FROM dblink_fetch('myconn','rmt_foo_cursor',4) AS t(a int, b text, c text[]);
 
257
 
 
258
-- this one only finds three rows left
 
259
SELECT *
 
260
FROM dblink_fetch('myconn','rmt_foo_cursor',4) AS t(a int, b text, c text[]);
 
261
 
 
262
-- fetch some data incorrectly
 
263
SELECT *
 
264
FROM dblink_fetch('myconn','rmt_foobar_cursor',4,false) AS t(a int, b text, c text[]);
 
265
 
 
266
-- reset remote transaction state
 
267
SELECT dblink_exec('myconn','ABORT');
 
268
 
 
269
-- should generate 'cursor "rmt_foo_cursor" not found' error
 
270
SELECT *
 
271
FROM dblink_fetch('myconn','rmt_foo_cursor',4) AS t(a int, b text, c text[]);
 
272
 
 
273
-- close the named persistent connection
 
274
SELECT dblink_disconnect('myconn');
 
275
 
 
276
-- should generate "missing "=" after "myconn" in connection info string" error
 
277
SELECT *
 
278
FROM dblink('myconn','SELECT * FROM foo') AS t(a int, b text, c text[])
 
279
WHERE t.a > 7;
 
280
 
 
281
-- create a named persistent connection
 
282
SELECT dblink_connect('myconn','dbname=contrib_regression');
 
283
 
 
284
-- put more data into our slave table, using named persistent connection syntax
 
285
-- but truncate the actual return value so we can use diff to check for success
 
286
SELECT substr(dblink_exec('myconn','INSERT INTO foo VALUES(11,''l'',''{"a11","b11","c11"}'')'),1,6);
 
287
 
 
288
-- let's see it
 
289
SELECT *
 
290
FROM dblink('myconn','SELECT * FROM foo') AS t(a int, b text, c text[]);
 
291
 
 
292
-- change some data
 
293
SELECT dblink_exec('myconn','UPDATE foo SET f3[2] = ''b99'' WHERE f1 = 11');
 
294
 
 
295
-- let's see it
 
296
SELECT *
 
297
FROM dblink('myconn','SELECT * FROM foo') AS t(a int, b text, c text[])
 
298
WHERE a = 11;
 
299
 
 
300
-- delete some data
 
301
SELECT dblink_exec('myconn','DELETE FROM foo WHERE f1 = 11');
 
302
 
 
303
-- let's see it
 
304
SELECT *
 
305
FROM dblink('myconn','SELECT * FROM foo') AS t(a int, b text, c text[])
 
306
WHERE a = 11;
 
307
 
 
308
-- close the named persistent connection
 
309
SELECT dblink_disconnect('myconn');
 
310
 
 
311
-- close the named persistent connection again
 
312
-- should get 'connection "myconn" not available' error
 
313
SELECT dblink_disconnect('myconn');
 
314
 
 
315
-- test asynchronous queries
 
316
SELECT dblink_connect('dtest1', 'dbname=contrib_regression');
 
317
SELECT * from
 
318
 dblink_send_query('dtest1', 'select * from foo where f1 < 3') as t1;
 
319
 
 
320
SELECT dblink_connect('dtest2', 'dbname=contrib_regression');
 
321
SELECT * from
 
322
 dblink_send_query('dtest2', 'select * from foo where f1 > 2 and f1 < 7') as t1;
 
323
 
 
324
SELECT dblink_connect('dtest3', 'dbname=contrib_regression');
 
325
SELECT * from
 
326
 dblink_send_query('dtest3', 'select * from foo where f1 > 6') as t1;
 
327
 
 
328
CREATE TEMPORARY TABLE result AS
 
329
(SELECT * from dblink_get_result('dtest1') as t1(f1 int, f2 text, f3 text[]))
 
330
UNION
 
331
(SELECT * from dblink_get_result('dtest2') as t2(f1 int, f2 text, f3 text[]))
 
332
UNION
 
333
(SELECT * from dblink_get_result('dtest3') as t3(f1 int, f2 text, f3 text[]))
 
334
ORDER by f1;
 
335
 
 
336
-- dblink_get_connections returns an array with elements in a machine-dependent
 
337
-- ordering, so we must resort to unnesting and sorting for a stable result
 
338
create function unnest(anyarray) returns setof anyelement
 
339
language sql strict immutable as $$
 
340
select $1[i] from generate_series(array_lower($1,1), array_upper($1,1)) as i
 
341
$$;
 
342
 
 
343
SELECT * FROM unnest(dblink_get_connections()) ORDER BY 1;
 
344
 
 
345
SELECT dblink_is_busy('dtest1');
 
346
 
 
347
SELECT dblink_disconnect('dtest1');
 
348
SELECT dblink_disconnect('dtest2');
 
349
SELECT dblink_disconnect('dtest3');
 
350
 
 
351
SELECT * from result;
 
352
 
 
353
SELECT dblink_connect('dtest1', 'dbname=contrib_regression');
 
354
SELECT * from
 
355
 dblink_send_query('dtest1', 'select * from foo where f1 < 3') as t1;
 
356
 
 
357
SELECT dblink_cancel_query('dtest1');
 
358
SELECT dblink_error_message('dtest1');
 
359
SELECT dblink_disconnect('dtest1');
 
360
 
 
361
-- test foreign data wrapper functionality
 
362
CREATE USER dblink_regression_test;
 
363
 
 
364
CREATE FOREIGN DATA WRAPPER postgresql;
 
365
CREATE SERVER fdtest FOREIGN DATA WRAPPER postgresql OPTIONS (dbname 'contrib_regression');
 
366
CREATE USER MAPPING FOR public SERVER fdtest;
 
367
GRANT USAGE ON FOREIGN SERVER fdtest TO dblink_regression_test;
 
368
GRANT EXECUTE ON FUNCTION dblink_connect_u(text, text) TO dblink_regression_test;
 
369
 
 
370
\set ORIGINAL_USER :USER
 
371
\c - dblink_regression_test
 
372
-- should fail
 
373
SELECT dblink_connect('myconn', 'fdtest');
 
374
-- should succeed
 
375
SELECT dblink_connect_u('myconn', 'fdtest');
 
376
SELECT * FROM dblink('myconn','SELECT * FROM foo') AS t(a int, b text, c text[]);
 
377
 
 
378
\c - :ORIGINAL_USER
 
379
REVOKE USAGE ON FOREIGN SERVER fdtest FROM dblink_regression_test;
 
380
REVOKE EXECUTE ON FUNCTION dblink_connect_u(text, text) FROM dblink_regression_test;
 
381
DROP USER dblink_regression_test;
 
382
DROP USER MAPPING FOR public SERVER fdtest;
 
383
DROP SERVER fdtest;
 
384
DROP FOREIGN DATA WRAPPER postgresql;
 
385
 
 
386
-- test asynchronous notifications
 
387
SELECT dblink_connect('dbname=contrib_regression');
 
388
 
 
389
--should return listen
 
390
SELECT dblink_exec('LISTEN regression');
 
391
--should return listen
 
392
SELECT dblink_exec('LISTEN foobar');
 
393
 
 
394
SELECT dblink_exec('NOTIFY regression');
 
395
SELECT dblink_exec('NOTIFY foobar');
 
396
 
 
397
SELECT notify_name, be_pid = (select t.be_pid from dblink('select pg_backend_pid()') as t(be_pid int)) AS is_self_notify, extra from dblink_get_notify();
 
398
 
 
399
SELECT * from dblink_get_notify();
 
400
 
 
401
SELECT dblink_disconnect();
 
402
 
 
403
-- test dropped columns in dblink_build_sql_insert, dblink_build_sql_update
 
404
CREATE TEMP TABLE test_dropped
 
405
(
 
406
        col1 INT NOT NULL DEFAULT 111,
 
407
        id SERIAL PRIMARY KEY,
 
408
        col2 INT NOT NULL DEFAULT 112,
 
409
        col2b INT NOT NULL DEFAULT 113
 
410
);
 
411
 
 
412
INSERT INTO test_dropped VALUES(default);
 
413
 
 
414
ALTER TABLE test_dropped
 
415
        DROP COLUMN col1,
 
416
        DROP COLUMN col2,
 
417
        ADD COLUMN col3 VARCHAR(10) NOT NULL DEFAULT 'foo',
 
418
        ADD COLUMN col4 INT NOT NULL DEFAULT 42;
 
419
 
 
420
SELECT dblink_build_sql_insert('test_dropped', '1', 1,
 
421
                               ARRAY['1'::TEXT], ARRAY['2'::TEXT]);
 
422
 
 
423
SELECT dblink_build_sql_update('test_dropped', '1', 1,
 
424
                               ARRAY['1'::TEXT], ARRAY['2'::TEXT]);
 
425
 
 
426
SELECT dblink_build_sql_delete('test_dropped', '1', 1,
 
427
                               ARRAY['2'::TEXT]);