← Back to branch summary

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

« back to all changes in this revision

Viewing changes to src/test/regress/expected/rangefuncs.out

  • 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:
src/test/regress/expected/rangefuncs.out
 
1
SELECT name, setting FROM pg_settings WHERE name LIKE 'enable%';
 
2
       name        | setting 
 
3
-------------------+---------
 
4
 enable_bitmapscan | on
 
5
 enable_hashagg    | on
 
6
 enable_hashjoin   | on
 
7
 enable_indexscan  | on
 
8
 enable_material   | on
 
9
 enable_mergejoin  | on
 
10
 enable_nestloop   | on
 
11
 enable_seqscan    | on
 
12
 enable_sort       | on
 
13
 enable_tidscan    | on
 
14
(10 rows)
 
15
 
 
16
CREATE TABLE foo2(fooid int, f2 int);
 
17
INSERT INTO foo2 VALUES(1, 11);
 
18
INSERT INTO foo2 VALUES(2, 22);
 
19
INSERT INTO foo2 VALUES(1, 111);
 
20
CREATE FUNCTION foot(int) returns setof foo2 as 'SELECT * FROM foo2 WHERE fooid = $1;' LANGUAGE SQL;
 
21
-- supposed to fail with ERROR
 
22
select * from foo2, foot(foo2.fooid) z where foo2.f2 = z.f2;
 
23
ERROR:  function expression in FROM cannot refer to other relations of same query level
 
24
LINE 1: select * from foo2, foot(foo2.fooid) z where foo2.f2 = z.f2;
 
25
                                 ^
 
26
-- function in subselect
 
27
select * from foo2 where f2 in (select f2 from foot(foo2.fooid) z where z.fooid = foo2.fooid) ORDER BY 1,2;
 
28
 fooid | f2  
 
29
-------+-----
 
30
     1 |  11
 
31
     1 | 111
 
32
     2 |  22
 
33
(3 rows)
 
34
 
 
35
-- function in subselect
 
36
select * from foo2 where f2 in (select f2 from foot(1) z where z.fooid = foo2.fooid) ORDER BY 1,2;
 
37
 fooid | f2  
 
38
-------+-----
 
39
     1 |  11
 
40
     1 | 111
 
41
(2 rows)
 
42
 
 
43
-- function in subselect
 
44
select * from foo2 where f2 in (select f2 from foot(foo2.fooid) z where z.fooid = 1) ORDER BY 1,2;
 
45
 fooid | f2  
 
46
-------+-----
 
47
     1 |  11
 
48
     1 | 111
 
49
(2 rows)
 
50
 
 
51
-- nested functions
 
52
select foot.fooid, foot.f2 from foot(sin(pi()/2)::int) ORDER BY 1,2;
 
53
 fooid | f2  
 
54
-------+-----
 
55
     1 |  11
 
56
     1 | 111
 
57
(2 rows)
 
58
 
 
59
CREATE TABLE foo (fooid int, foosubid int, fooname text, primary key(fooid,foosubid));
 
60
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "foo_pkey" for table "foo"
 
61
INSERT INTO foo VALUES(1,1,'Joe');
 
62
INSERT INTO foo VALUES(1,2,'Ed');
 
63
INSERT INTO foo VALUES(2,1,'Mary');
 
64
-- sql, proretset = f, prorettype = b
 
65
CREATE FUNCTION getfoo(int) RETURNS int AS 'SELECT $1;' LANGUAGE SQL;
 
66
SELECT * FROM getfoo(1) AS t1;
 
67
 t1 
 
68
----
 
69
  1
 
70
(1 row)
 
71
 
 
72
CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);
 
73
SELECT * FROM vw_getfoo;
 
74
 getfoo 
 
75
--------
 
76
      1
 
77
(1 row)
 
78
 
 
79
-- sql, proretset = t, prorettype = b
 
80
DROP VIEW vw_getfoo;
 
81
DROP FUNCTION getfoo(int);
 
82
CREATE FUNCTION getfoo(int) RETURNS setof int AS 'SELECT fooid FROM foo WHERE fooid = $1;' LANGUAGE SQL;
 
83
SELECT * FROM getfoo(1) AS t1;
 
84
 t1 
 
85
----
 
86
  1
 
87
  1
 
88
(2 rows)
 
89
 
 
90
CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);
 
91
SELECT * FROM vw_getfoo;
 
92
 getfoo 
 
93
--------
 
94
      1
 
95
      1
 
96
(2 rows)
 
97
 
 
98
-- sql, proretset = t, prorettype = b
 
99
DROP VIEW vw_getfoo;
 
100
DROP FUNCTION getfoo(int);
 
101
CREATE FUNCTION getfoo(int) RETURNS setof text AS 'SELECT fooname FROM foo WHERE fooid = $1;' LANGUAGE SQL;
 
102
SELECT * FROM getfoo(1) AS t1;
 
103
 t1  
 
104
-----
 
105
 Joe
 
106
 Ed
 
107
(2 rows)
 
108
 
 
109
CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);
 
110
SELECT * FROM vw_getfoo;
 
111
 getfoo 
 
112
--------
 
113
 Joe
 
114
 Ed
 
115
(2 rows)
 
116
 
 
117
-- sql, proretset = f, prorettype = c
 
118
DROP VIEW vw_getfoo;
 
119
DROP FUNCTION getfoo(int);
 
120
CREATE FUNCTION getfoo(int) RETURNS foo AS 'SELECT * FROM foo WHERE fooid = $1;' LANGUAGE SQL;
 
121
SELECT * FROM getfoo(1) AS t1;
 
122
 fooid | foosubid | fooname 
 
123
-------+----------+---------
 
124
     1 |        1 | Joe
 
125
(1 row)
 
126
 
 
127
CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);
 
128
SELECT * FROM vw_getfoo;
 
129
 fooid | foosubid | fooname 
 
130
-------+----------+---------
 
131
     1 |        1 | Joe
 
132
(1 row)
 
133
 
 
134
-- sql, proretset = t, prorettype = c
 
135
DROP VIEW vw_getfoo;
 
136
DROP FUNCTION getfoo(int);
 
137
CREATE FUNCTION getfoo(int) RETURNS setof foo AS 'SELECT * FROM foo WHERE fooid = $1;' LANGUAGE SQL;
 
138
SELECT * FROM getfoo(1) AS t1;
 
139
 fooid | foosubid | fooname 
 
140
-------+----------+---------
 
141
     1 |        1 | Joe
 
142
     1 |        2 | Ed
 
143
(2 rows)
 
144
 
 
145
CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);
 
146
SELECT * FROM vw_getfoo;
 
147
 fooid | foosubid | fooname 
 
148
-------+----------+---------
 
149
     1 |        1 | Joe
 
150
     1 |        2 | Ed
 
151
(2 rows)
 
152
 
 
153
-- sql, proretset = f, prorettype = record
 
154
DROP VIEW vw_getfoo;
 
155
DROP FUNCTION getfoo(int);
 
156
CREATE FUNCTION getfoo(int) RETURNS RECORD AS 'SELECT * FROM foo WHERE fooid = $1;' LANGUAGE SQL;
 
157
SELECT * FROM getfoo(1) AS t1(fooid int, foosubid int, fooname text);
 
158
 fooid | foosubid | fooname 
 
159
-------+----------+---------
 
160
     1 |        1 | Joe
 
161
(1 row)
 
162
 
 
163
CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1) AS
 
164
(fooid int, foosubid int, fooname text);
 
165
SELECT * FROM vw_getfoo;
 
166
 fooid | foosubid | fooname 
 
167
-------+----------+---------
 
168
     1 |        1 | Joe
 
169
(1 row)
 
170
 
 
171
-- sql, proretset = t, prorettype = record
 
172
DROP VIEW vw_getfoo;
 
173
DROP FUNCTION getfoo(int);
 
174
CREATE FUNCTION getfoo(int) RETURNS setof record AS 'SELECT * FROM foo WHERE fooid = $1;' LANGUAGE SQL;
 
175
SELECT * FROM getfoo(1) AS t1(fooid int, foosubid int, fooname text);
 
176
 fooid | foosubid | fooname 
 
177
-------+----------+---------
 
178
     1 |        1 | Joe
 
179
     1 |        2 | Ed
 
180
(2 rows)
 
181
 
 
182
CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1) AS
 
183
(fooid int, foosubid int, fooname text);
 
184
SELECT * FROM vw_getfoo;
 
185
 fooid | foosubid | fooname 
 
186
-------+----------+---------
 
187
     1 |        1 | Joe
 
188
     1 |        2 | Ed
 
189
(2 rows)
 
190
 
 
191
-- plpgsql, proretset = f, prorettype = b
 
192
DROP VIEW vw_getfoo;
 
193
DROP FUNCTION getfoo(int);
 
194
CREATE FUNCTION getfoo(int) RETURNS int AS 'DECLARE fooint int; BEGIN SELECT fooid into fooint FROM foo WHERE fooid = $1; RETURN fooint; END;' LANGUAGE plpgsql;
 
195
SELECT * FROM getfoo(1) AS t1;
 
196
 t1 
 
197
----
 
198
  1
 
199
(1 row)
 
200
 
 
201
CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);
 
202
SELECT * FROM vw_getfoo;
 
203
 getfoo 
 
204
--------
 
205
      1
 
206
(1 row)
 
207
 
 
208
-- plpgsql, proretset = f, prorettype = c
 
209
DROP VIEW vw_getfoo;
 
210
DROP FUNCTION getfoo(int);
 
211
CREATE FUNCTION getfoo(int) RETURNS foo AS 'DECLARE footup foo%ROWTYPE; BEGIN SELECT * into footup FROM foo WHERE fooid = $1; RETURN footup; END;' LANGUAGE plpgsql;
 
212
SELECT * FROM getfoo(1) AS t1;
 
213
 fooid | foosubid | fooname 
 
214
-------+----------+---------
 
215
     1 |        1 | Joe
 
216
(1 row)
 
217
 
 
218
CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);
 
219
SELECT * FROM vw_getfoo;
 
220
 fooid | foosubid | fooname 
 
221
-------+----------+---------
 
222
     1 |        1 | Joe
 
223
(1 row)
 
224
 
 
225
DROP VIEW vw_getfoo;
 
226
DROP FUNCTION getfoo(int);
 
227
DROP FUNCTION foot(int);
 
228
DROP TABLE foo2;
 
229
DROP TABLE foo;
 
230
-- Rescan tests --
 
231
CREATE TABLE foorescan (fooid int, foosubid int, fooname text, primary key(fooid,foosubid));
 
232
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "foorescan_pkey" for table "foorescan"
 
233
INSERT INTO foorescan values(5000,1,'abc.5000.1');
 
234
INSERT INTO foorescan values(5001,1,'abc.5001.1');
 
235
INSERT INTO foorescan values(5002,1,'abc.5002.1');
 
236
INSERT INTO foorescan values(5003,1,'abc.5003.1');
 
237
INSERT INTO foorescan values(5004,1,'abc.5004.1');
 
238
INSERT INTO foorescan values(5005,1,'abc.5005.1');
 
239
INSERT INTO foorescan values(5006,1,'abc.5006.1');
 
240
INSERT INTO foorescan values(5007,1,'abc.5007.1');
 
241
INSERT INTO foorescan values(5008,1,'abc.5008.1');
 
242
INSERT INTO foorescan values(5009,1,'abc.5009.1');
 
243
INSERT INTO foorescan values(5000,2,'abc.5000.2');
 
244
INSERT INTO foorescan values(5001,2,'abc.5001.2');
 
245
INSERT INTO foorescan values(5002,2,'abc.5002.2');
 
246
INSERT INTO foorescan values(5003,2,'abc.5003.2');
 
247
INSERT INTO foorescan values(5004,2,'abc.5004.2');
 
248
INSERT INTO foorescan values(5005,2,'abc.5005.2');
 
249
INSERT INTO foorescan values(5006,2,'abc.5006.2');
 
250
INSERT INTO foorescan values(5007,2,'abc.5007.2');
 
251
INSERT INTO foorescan values(5008,2,'abc.5008.2');
 
252
INSERT INTO foorescan values(5009,2,'abc.5009.2');
 
253
INSERT INTO foorescan values(5000,3,'abc.5000.3');
 
254
INSERT INTO foorescan values(5001,3,'abc.5001.3');
 
255
INSERT INTO foorescan values(5002,3,'abc.5002.3');
 
256
INSERT INTO foorescan values(5003,3,'abc.5003.3');
 
257
INSERT INTO foorescan values(5004,3,'abc.5004.3');
 
258
INSERT INTO foorescan values(5005,3,'abc.5005.3');
 
259
INSERT INTO foorescan values(5006,3,'abc.5006.3');
 
260
INSERT INTO foorescan values(5007,3,'abc.5007.3');
 
261
INSERT INTO foorescan values(5008,3,'abc.5008.3');
 
262
INSERT INTO foorescan values(5009,3,'abc.5009.3');
 
263
INSERT INTO foorescan values(5000,4,'abc.5000.4');
 
264
INSERT INTO foorescan values(5001,4,'abc.5001.4');
 
265
INSERT INTO foorescan values(5002,4,'abc.5002.4');
 
266
INSERT INTO foorescan values(5003,4,'abc.5003.4');
 
267
INSERT INTO foorescan values(5004,4,'abc.5004.4');
 
268
INSERT INTO foorescan values(5005,4,'abc.5005.4');
 
269
INSERT INTO foorescan values(5006,4,'abc.5006.4');
 
270
INSERT INTO foorescan values(5007,4,'abc.5007.4');
 
271
INSERT INTO foorescan values(5008,4,'abc.5008.4');
 
272
INSERT INTO foorescan values(5009,4,'abc.5009.4');
 
273
INSERT INTO foorescan values(5000,5,'abc.5000.5');
 
274
INSERT INTO foorescan values(5001,5,'abc.5001.5');
 
275
INSERT INTO foorescan values(5002,5,'abc.5002.5');
 
276
INSERT INTO foorescan values(5003,5,'abc.5003.5');
 
277
INSERT INTO foorescan values(5004,5,'abc.5004.5');
 
278
INSERT INTO foorescan values(5005,5,'abc.5005.5');
 
279
INSERT INTO foorescan values(5006,5,'abc.5006.5');
 
280
INSERT INTO foorescan values(5007,5,'abc.5007.5');
 
281
INSERT INTO foorescan values(5008,5,'abc.5008.5');
 
282
INSERT INTO foorescan values(5009,5,'abc.5009.5');
 
283
CREATE FUNCTION foorescan(int,int) RETURNS setof foorescan AS 'SELECT * FROM foorescan WHERE fooid >= $1 and fooid < $2 ;' LANGUAGE SQL;
 
284
--invokes ExecReScanFunctionScan
 
285
SELECT * FROM foorescan f WHERE f.fooid IN (SELECT fooid FROM foorescan(5002,5004)) ORDER BY 1,2;
 
286
 fooid | foosubid |  fooname   
 
287
-------+----------+------------
 
288
  5002 |        1 | abc.5002.1
 
289
  5002 |        2 | abc.5002.2
 
290
  5002 |        3 | abc.5002.3
 
291
  5002 |        4 | abc.5002.4
 
292
  5002 |        5 | abc.5002.5
 
293
  5003 |        1 | abc.5003.1
 
294
  5003 |        2 | abc.5003.2
 
295
  5003 |        3 | abc.5003.3
 
296
  5003 |        4 | abc.5003.4
 
297
  5003 |        5 | abc.5003.5
 
298
(10 rows)
 
299
 
 
300
CREATE VIEW vw_foorescan AS SELECT * FROM foorescan(5002,5004);
 
301
--invokes ExecReScanFunctionScan
 
302
SELECT * FROM foorescan f WHERE f.fooid IN (SELECT fooid FROM vw_foorescan) ORDER BY 1,2;
 
303
 fooid | foosubid |  fooname   
 
304
-------+----------+------------
 
305
  5002 |        1 | abc.5002.1
 
306
  5002 |        2 | abc.5002.2
 
307
  5002 |        3 | abc.5002.3
 
308
  5002 |        4 | abc.5002.4
 
309
  5002 |        5 | abc.5002.5
 
310
  5003 |        1 | abc.5003.1
 
311
  5003 |        2 | abc.5003.2
 
312
  5003 |        3 | abc.5003.3
 
313
  5003 |        4 | abc.5003.4
 
314
  5003 |        5 | abc.5003.5
 
315
(10 rows)
 
316
 
 
317
CREATE TABLE barrescan (fooid int primary key);
 
318
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "barrescan_pkey" for table "barrescan"
 
319
INSERT INTO barrescan values(5003);
 
320
INSERT INTO barrescan values(5004);
 
321
INSERT INTO barrescan values(5005);
 
322
INSERT INTO barrescan values(5006);
 
323
INSERT INTO barrescan values(5007);
 
324
INSERT INTO barrescan values(5008);
 
325
CREATE FUNCTION foorescan(int) RETURNS setof foorescan AS 'SELECT * FROM foorescan WHERE fooid = $1;' LANGUAGE SQL;
 
326
--invokes ExecReScanFunctionScan with chgParam != NULL
 
327
SELECT f.* FROM barrescan b, foorescan f WHERE f.fooid = b.fooid AND b.fooid IN (SELECT fooid FROM foorescan(b.fooid)) ORDER BY 1,2;
 
328
 fooid | foosubid |  fooname   
 
329
-------+----------+------------
 
330
  5003 |        1 | abc.5003.1
 
331
  5003 |        2 | abc.5003.2
 
332
  5003 |        3 | abc.5003.3
 
333
  5003 |        4 | abc.5003.4
 
334
  5003 |        5 | abc.5003.5
 
335
  5004 |        1 | abc.5004.1
 
336
  5004 |        2 | abc.5004.2
 
337
  5004 |        3 | abc.5004.3
 
338
  5004 |        4 | abc.5004.4
 
339
  5004 |        5 | abc.5004.5
 
340
  5005 |        1 | abc.5005.1
 
341
  5005 |        2 | abc.5005.2
 
342
  5005 |        3 | abc.5005.3
 
343
  5005 |        4 | abc.5005.4
 
344
  5005 |        5 | abc.5005.5
 
345
  5006 |        1 | abc.5006.1
 
346
  5006 |        2 | abc.5006.2
 
347
  5006 |        3 | abc.5006.3
 
348
  5006 |        4 | abc.5006.4
 
349
  5006 |        5 | abc.5006.5
 
350
  5007 |        1 | abc.5007.1
 
351
  5007 |        2 | abc.5007.2
 
352
  5007 |        3 | abc.5007.3
 
353
  5007 |        4 | abc.5007.4
 
354
  5007 |        5 | abc.5007.5
 
355
  5008 |        1 | abc.5008.1
 
356
  5008 |        2 | abc.5008.2
 
357
  5008 |        3 | abc.5008.3
 
358
  5008 |        4 | abc.5008.4
 
359
  5008 |        5 | abc.5008.5
 
360
(30 rows)
 
361
 
 
362
SELECT b.fooid, max(f.foosubid) FROM barrescan b, foorescan f WHERE f.fooid = b.fooid AND b.fooid IN (SELECT fooid FROM foorescan(b.fooid)) GROUP BY b.fooid ORDER BY 1,2;
 
363
 fooid | max 
 
364
-------+-----
 
365
  5003 |   5
 
366
  5004 |   5
 
367
  5005 |   5
 
368
  5006 |   5
 
369
  5007 |   5
 
370
  5008 |   5
 
371
(6 rows)
 
372
 
 
373
CREATE VIEW fooview1 AS SELECT f.* FROM barrescan b, foorescan f WHERE f.fooid = b.fooid AND b.fooid IN (SELECT fooid FROM foorescan(b.fooid)) ORDER BY 1,2;
 
374
SELECT * FROM fooview1 AS fv WHERE fv.fooid = 5004;
 
375
 fooid | foosubid |  fooname   
 
376
-------+----------+------------
 
377
  5004 |        1 | abc.5004.1
 
378
  5004 |        2 | abc.5004.2
 
379
  5004 |        3 | abc.5004.3
 
380
  5004 |        4 | abc.5004.4
 
381
  5004 |        5 | abc.5004.5
 
382
(5 rows)
 
383
 
 
384
CREATE VIEW fooview2 AS SELECT b.fooid, max(f.foosubid) AS maxsubid FROM barrescan b, foorescan f WHERE f.fooid = b.fooid AND b.fooid IN (SELECT fooid FROM foorescan(b.fooid)) GROUP BY b.fooid ORDER BY 1,2;
 
385
SELECT * FROM fooview2 AS fv WHERE fv.maxsubid = 5;
 
386
 fooid | maxsubid 
 
387
-------+----------
 
388
  5003 |        5
 
389
  5004 |        5
 
390
  5005 |        5
 
391
  5006 |        5
 
392
  5007 |        5
 
393
  5008 |        5
 
394
(6 rows)
 
395
 
 
396
DROP VIEW vw_foorescan;
 
397
DROP VIEW fooview1;
 
398
DROP VIEW fooview2;
 
399
DROP FUNCTION foorescan(int,int);
 
400
DROP FUNCTION foorescan(int);
 
401
DROP TABLE foorescan;
 
402
DROP TABLE barrescan;
 
403
--
 
404
-- Test cases involving OUT parameters
 
405
--
 
406
CREATE FUNCTION foo(in f1 int, out f2 int)
 
407
AS 'select $1+1' LANGUAGE sql;
 
408
SELECT foo(42);
 
409
 foo 
 
410
-----
 
411
  43
 
412
(1 row)
 
413
 
 
414
SELECT * FROM foo(42);
 
415
 f2 
 
416
----
 
417
 43
 
418
(1 row)
 
419
 
 
420
SELECT * FROM foo(42) AS p(x);
 
421
 x  
 
422
----
 
423
 43
 
424
(1 row)
 
425
 
 
426
-- explicit spec of return type is OK
 
427
CREATE OR REPLACE FUNCTION foo(in f1 int, out f2 int) RETURNS int
 
428
AS 'select $1+1' LANGUAGE sql;
 
429
-- error, wrong result type
 
430
CREATE OR REPLACE FUNCTION foo(in f1 int, out f2 int) RETURNS float
 
431
AS 'select $1+1' LANGUAGE sql;
 
432
ERROR:  function result type must be integer because of OUT parameters
 
433
-- with multiple OUT params you must get a RECORD result
 
434
CREATE OR REPLACE FUNCTION foo(in f1 int, out f2 int, out f3 text) RETURNS int
 
435
AS 'select $1+1' LANGUAGE sql;
 
436
ERROR:  function result type must be record because of OUT parameters
 
437
CREATE OR REPLACE FUNCTION foo(in f1 int, out f2 int, out f3 text)
 
438
RETURNS record
 
439
AS 'select $1+1' LANGUAGE sql;
 
440
ERROR:  cannot change return type of existing function
 
441
HINT:  Use DROP FUNCTION first.
 
442
CREATE OR REPLACE FUNCTION foor(in f1 int, out f2 int, out text)
 
443
AS $$select $1-1, $1::text || 'z'$$ LANGUAGE sql;
 
444
SELECT f1, foor(f1) FROM int4_tbl;
 
445
     f1      |            foor            
 
446
-------------+----------------------------
 
447
           0 | (-1,0z)
 
448
      123456 | (123455,123456z)
 
449
     -123456 | (-123457,-123456z)
 
450
  2147483647 | (2147483646,2147483647z)
 
451
 -2147483647 | (-2147483648,-2147483647z)
 
452
(5 rows)
 
453
 
 
454
SELECT * FROM foor(42);
 
455
 f2 | column2 
 
456
----+---------
 
457
 41 | 42z
 
458
(1 row)
 
459
 
 
460
SELECT * FROM foor(42) AS p(a,b);
 
461
 a  |  b  
 
462
----+-----
 
463
 41 | 42z
 
464
(1 row)
 
465
 
 
466
CREATE OR REPLACE FUNCTION foob(in f1 int, inout f2 int, out text)
 
467
AS $$select $2-1, $1::text || 'z'$$ LANGUAGE sql;
 
468
SELECT f1, foob(f1, f1/2) FROM int4_tbl;
 
469
     f1      |            foob            
 
470
-------------+----------------------------
 
471
           0 | (-1,0z)
 
472
      123456 | (61727,123456z)
 
473
     -123456 | (-61729,-123456z)
 
474
  2147483647 | (1073741822,2147483647z)
 
475
 -2147483647 | (-1073741824,-2147483647z)
 
476
(5 rows)
 
477
 
 
478
SELECT * FROM foob(42, 99);
 
479
 f2 | column2 
 
480
----+---------
 
481
 98 | 42z
 
482
(1 row)
 
483
 
 
484
SELECT * FROM foob(42, 99) AS p(a,b);
 
485
 a  |  b  
 
486
----+-----
 
487
 98 | 42z
 
488
(1 row)
 
489
 
 
490
-- Can reference function with or without OUT params for DROP, etc
 
491
DROP FUNCTION foo(int);
 
492
DROP FUNCTION foor(in f2 int, out f1 int, out text);
 
493
DROP FUNCTION foob(in f1 int, inout f2 int);
 
494
--
 
495
-- For my next trick, polymorphic OUT parameters
 
496
--
 
497
CREATE FUNCTION dup (f1 anyelement, f2 out anyelement, f3 out anyarray)
 
498
AS 'select $1, array[$1,$1]' LANGUAGE sql;
 
499
SELECT dup(22);
 
500
      dup       
 
501
----------------
 
502
 (22,"{22,22}")
 
503
(1 row)
 
504
 
 
505
SELECT dup('xyz');      -- fails
 
506
ERROR:  could not determine polymorphic type because input has type "unknown"
 
507
SELECT dup('xyz'::text);
 
508
        dup        
 
509
-------------------
 
510
 (xyz,"{xyz,xyz}")
 
511
(1 row)
 
512
 
 
513
SELECT * FROM dup('xyz'::text);
 
514
 f2  |    f3     
 
515
-----+-----------
 
516
 xyz | {xyz,xyz}
 
517
(1 row)
 
518
 
 
519
-- fails, as we are attempting to rename first argument
 
520
CREATE OR REPLACE FUNCTION dup (inout f2 anyelement, out f3 anyarray)
 
521
AS 'select $1, array[$1,$1]' LANGUAGE sql;
 
522
ERROR:  cannot change name of input parameter "f1"
 
523
HINT:  Use DROP FUNCTION first.
 
524
DROP FUNCTION dup(anyelement);
 
525
-- equivalent behavior, though different name exposed for input arg
 
526
CREATE OR REPLACE FUNCTION dup (inout f2 anyelement, out f3 anyarray)
 
527
AS 'select $1, array[$1,$1]' LANGUAGE sql;
 
528
SELECT dup(22);
 
529
      dup       
 
530
----------------
 
531
 (22,"{22,22}")
 
532
(1 row)
 
533
 
 
534
DROP FUNCTION dup(anyelement);
 
535
-- fails, no way to deduce outputs
 
536
CREATE FUNCTION bad (f1 int, out f2 anyelement, out f3 anyarray)
 
537
AS 'select $1, array[$1,$1]' LANGUAGE sql;
 
538
ERROR:  cannot determine result data type
 
539
DETAIL:  A function returning a polymorphic type must have at least one polymorphic argument.
 
540
--
 
541
-- table functions
 
542
--
 
543
CREATE OR REPLACE FUNCTION foo()
 
544
RETURNS TABLE(a int)
 
545
AS $$ SELECT a FROM generate_series(1,5) a(a) $$ LANGUAGE sql;
 
546
SELECT * FROM foo();
 
547
 a 
 
548
---
 
549
 1
 
550
 2
 
551
 3
 
552
 4
 
553
 5
 
554
(5 rows)
 
555
 
 
556
DROP FUNCTION foo();
 
557
CREATE OR REPLACE FUNCTION foo(int)
 
558
RETURNS TABLE(a int, b int)
 
559
AS $$ SELECT a, b
 
560
         FROM generate_series(1,$1) a(a),
 
561
              generate_series(1,$1) b(b) $$ LANGUAGE sql;
 
562
SELECT * FROM foo(3);
 
563
 a | b 
 
564
---+---
 
565
 1 | 1
 
566
 1 | 2
 
567
 1 | 3
 
568
 2 | 1
 
569
 2 | 2
 
570
 2 | 3
 
571
 3 | 1
 
572
 3 | 2
 
573
 3 | 3
 
574
(9 rows)
 
575
 
 
576
DROP FUNCTION foo(int);
 
577
--
 
578
-- some tests on SQL functions with RETURNING
 
579
--
 
580
create temp table tt(f1 serial, data text);
 
581
NOTICE:  CREATE TABLE will create implicit sequence "tt_f1_seq" for serial column "tt.f1"
 
582
create function insert_tt(text) returns int as
 
583
$$ insert into tt(data) values($1) returning f1 $$
 
584
language sql;
 
585
select insert_tt('foo');
 
586
 insert_tt 
 
587
-----------
 
588
         1
 
589
(1 row)
 
590
 
 
591
select insert_tt('bar');
 
592
 insert_tt 
 
593
-----------
 
594
         2
 
595
(1 row)
 
596
 
 
597
select * from tt;
 
598
 f1 | data 
 
599
----+------
 
600
  1 | foo
 
601
  2 | bar
 
602
(2 rows)
 
603
 
 
604
-- insert will execute to completion even if function needs just 1 row
 
605
create or replace function insert_tt(text) returns int as
 
606
$$ insert into tt(data) values($1),($1||$1) returning f1 $$
 
607
language sql;
 
608
select insert_tt('fool');
 
609
 insert_tt 
 
610
-----------
 
611
         3
 
612
(1 row)
 
613
 
 
614
select * from tt;
 
615
 f1 |   data   
 
616
----+----------
 
617
  1 | foo
 
618
  2 | bar
 
619
  3 | fool
 
620
  4 | foolfool
 
621
(4 rows)
 
622
 
 
623
-- setof does what's expected
 
624
create or replace function insert_tt2(text,text) returns setof int as
 
625
$$ insert into tt(data) values($1),($2) returning f1 $$
 
626
language sql;
 
627
select insert_tt2('foolish','barrish');
 
628
 insert_tt2 
 
629
------------
 
630
          5
 
631
          6
 
632
(2 rows)
 
633
 
 
634
select * from insert_tt2('baz','quux');
 
635
 insert_tt2 
 
636
------------
 
637
          7
 
638
          8
 
639
(2 rows)
 
640
 
 
641
select * from tt;
 
642
 f1 |   data   
 
643
----+----------
 
644
  1 | foo
 
645
  2 | bar
 
646
  3 | fool
 
647
  4 | foolfool
 
648
  5 | foolish
 
649
  6 | barrish
 
650
  7 | baz
 
651
  8 | quux
 
652
(8 rows)
 
653
 
 
654
-- limit doesn't prevent execution to completion
 
655
select insert_tt2('foolish','barrish') limit 1;
 
656
 insert_tt2 
 
657
------------
 
658
          9
 
659
(1 row)
 
660
 
 
661
select * from tt;
 
662
 f1 |   data   
 
663
----+----------
 
664
  1 | foo
 
665
  2 | bar
 
666
  3 | fool
 
667
  4 | foolfool
 
668
  5 | foolish
 
669
  6 | barrish
 
670
  7 | baz
 
671
  8 | quux
 
672
  9 | foolish
 
673
 10 | barrish
 
674
(10 rows)
 
675
 
 
676
-- triggers will fire, too
 
677
create function noticetrigger() returns trigger as $$
 
678
begin
 
679
  raise notice 'noticetrigger % %', new.f1, new.data;
 
680
  return null;
 
681
end $$ language plpgsql;
 
682
create trigger tnoticetrigger after insert on tt for each row
 
683
execute procedure noticetrigger();
 
684
select insert_tt2('foolme','barme') limit 1;
 
685
NOTICE:  noticetrigger 11 foolme
 
686
CONTEXT:  SQL function "insert_tt2" statement 1
 
687
NOTICE:  noticetrigger 12 barme
 
688
CONTEXT:  SQL function "insert_tt2" statement 1
 
689
 insert_tt2 
 
690
------------
 
691
         11
 
692
(1 row)
 
693
 
 
694
select * from tt;
 
695
 f1 |   data   
 
696
----+----------
 
697
  1 | foo
 
698
  2 | bar
 
699
  3 | fool
 
700
  4 | foolfool
 
701
  5 | foolish
 
702
  6 | barrish
 
703
  7 | baz
 
704
  8 | quux
 
705
  9 | foolish
 
706
 10 | barrish
 
707
 11 | foolme
 
708
 12 | barme
 
709
(12 rows)
 
710
 
 
711
-- and rules work
 
712
create temp table tt_log(f1 int, data text);
 
713
create rule insert_tt_rule as on insert to tt do also
 
714
  insert into tt_log values(new.*);
 
715
select insert_tt2('foollog','barlog') limit 1;
 
716
NOTICE:  noticetrigger 13 foollog
 
717
CONTEXT:  SQL function "insert_tt2" statement 1
 
718
NOTICE:  noticetrigger 14 barlog
 
719
CONTEXT:  SQL function "insert_tt2" statement 1
 
720
 insert_tt2 
 
721
------------
 
722
         13
 
723
(1 row)
 
724
 
 
725
select * from tt;
 
726
 f1 |   data   
 
727
----+----------
 
728
  1 | foo
 
729
  2 | bar
 
730
  3 | fool
 
731
  4 | foolfool
 
732
  5 | foolish
 
733
  6 | barrish
 
734
  7 | baz
 
735
  8 | quux
 
736
  9 | foolish
 
737
 10 | barrish
 
738
 11 | foolme
 
739
 12 | barme
 
740
 13 | foollog
 
741
 14 | barlog
 
742
(14 rows)
 
743
 
 
744
-- note that nextval() gets executed a second time in the rule expansion,
 
745
-- which is expected.
 
746
select * from tt_log;
 
747
 f1 |  data   
 
748
----+---------
 
749
 15 | foollog
 
750
 16 | barlog
 
751
(2 rows)
 
752
 
 
753
-- test case for a whole-row-variable bug
 
754
create function foo1(n integer, out a text, out b text)
 
755
  returns setof record
 
756
  language sql
 
757
  as $$ select 'foo ' || i, 'bar ' || i from generate_series(1,$1) i $$;
 
758
set work_mem='64kB';
 
759
select t.a, t, t.a from foo1(10000) t limit 1;
 
760
   a   |         t         |   a   
 
761
-------+-------------------+-------
 
762
 foo 1 | ("foo 1","bar 1") | foo 1
 
763
(1 row)
 
764
 
 
765
reset work_mem;
 
766
select t.a, t, t.a from foo1(10000) t limit 1;
 
767
   a   |         t         |   a   
 
768
-------+-------------------+-------
 
769
 foo 1 | ("foo 1","bar 1") | foo 1
 
770
(1 row)
 
771
 
 
772
drop function foo1(n integer);
 
773
-- test use of SQL functions returning record
 
774
-- this is supported in some cases where the query doesn't specify
 
775
-- the actual record type ...
 
776
create function array_to_set(anyarray) returns setof record as $$
 
777
  select i AS "index", $1[i] AS "value" from generate_subscripts($1, 1) i
 
778
$$ language sql strict immutable;
 
779
select array_to_set(array['one', 'two']);
 
780
 array_to_set 
 
781
--------------
 
782
 (1,one)
 
783
 (2,two)
 
784
(2 rows)
 
785
 
 
786
select * from array_to_set(array['one', 'two']) as t(f1 int,f2 text);
 
787
 f1 | f2  
 
788
----+-----
 
789
  1 | one
 
790
  2 | two
 
791
(2 rows)
 
792
 
 
793
select * from array_to_set(array['one', 'two']); -- fail
 
794
ERROR:  a column definition list is required for functions returning "record"
 
795
LINE 1: select * from array_to_set(array['one', 'two']);
 
796
                      ^
 
797
create temp table foo(f1 int8, f2 int8);
 
798
create function testfoo() returns record as $$
 
799
  insert into foo values (1,2) returning *;
 
800
$$ language sql;
 
801
select testfoo();
 
802
 testfoo 
 
803
---------
 
804
 (1,2)
 
805
(1 row)
 
806
 
 
807
select * from testfoo() as t(f1 int8,f2 int8);
 
808
 f1 | f2 
 
809
----+----
 
810
  1 |  2
 
811
(1 row)
 
812
 
 
813
select * from testfoo(); -- fail
 
814
ERROR:  a column definition list is required for functions returning "record"
 
815
LINE 1: select * from testfoo();
 
816
                      ^
 
817
drop function testfoo();
 
818
create function testfoo() returns setof record as $$
 
819
  insert into foo values (1,2), (3,4) returning *;
 
820
$$ language sql;
 
821
select testfoo();
 
822
 testfoo 
 
823
---------
 
824
 (1,2)
 
825
 (3,4)
 
826
(2 rows)
 
827
 
 
828
select * from testfoo() as t(f1 int8,f2 int8);
 
829
 f1 | f2 
 
830
----+----
 
831
  1 |  2
 
832
  3 |  4
 
833
(2 rows)
 
834
 
 
835
select * from testfoo(); -- fail
 
836
ERROR:  a column definition list is required for functions returning "record"
 
837
LINE 1: select * from testfoo();
 
838
                      ^
 
839
drop function testfoo();
 
840
--
 
841
-- Check some cases involving dropped columns in a rowtype result
 
842
--
 
843
create temp table users (userid text, email text, todrop bool, enabled bool);
 
844
insert into users values ('id','email',true,true);
 
845
insert into users values ('id2','email2',true,true);
 
846
alter table users drop column todrop;
 
847
create or replace function get_first_user() returns users as
 
848
$$ SELECT * FROM users ORDER BY userid LIMIT 1; $$
 
849
language sql stable;
 
850
SELECT get_first_user();
 
851
 get_first_user 
 
852
----------------
 
853
 (id,email,t)
 
854
(1 row)
 
855
 
 
856
SELECT * FROM get_first_user();
 
857
 userid | email | enabled 
 
858
--------+-------+---------
 
859
 id     | email | t
 
860
(1 row)
 
861
 
 
862
create or replace function get_users() returns setof users as
 
863
$$ SELECT * FROM users ORDER BY userid; $$
 
864
language sql stable;
 
865
SELECT get_users();
 
866
   get_users    
 
867
----------------
 
868
 (id,email,t)
 
869
 (id2,email2,t)
 
870
(2 rows)
 
871
 
 
872
SELECT * FROM get_users();
 
873
 userid | email  | enabled 
 
874
--------+--------+---------
 
875
 id     | email  | t
 
876
 id2    | email2 | t
 
877
(2 rows)
 
878
 
 
879
drop function get_first_user();
 
880
drop function get_users();
 
881
drop table users;
 
882
-- this won't get inlined because of type coercion, but it shouldn't fail
 
883
create or replace function foobar() returns setof text as
 
884
$$ select 'foo'::varchar union all select 'bar'::varchar ; $$
 
885
language sql stable;
 
886
select foobar();
 
887
 foobar 
 
888
--------
 
889
 foo
 
890
 bar
 
891
(2 rows)
 
892
 
 
893
select * from foobar();
 
894
 foobar 
 
895
--------
 
896
 foo
 
897
 bar
 
898
(2 rows)
 
899
 
 
900
drop function foobar();
 
901
-- check handling of a SQL function with multiple OUT params (bug #5777)
 
902
create or replace function foobar(out integer, out numeric) as
 
903
$$ select (1, 2.1) $$ language sql;
 
904
select * from foobar();
 
905
 column1 | column2 
 
906
---------+---------
 
907
       1 |     2.1
 
908
(1 row)
 
909
 
 
910
create or replace function foobar(out integer, out numeric) as
 
911
$$ select (1, 2) $$ language sql;
 
912
select * from foobar();  -- fail
 
913
ERROR:  function return row and query-specified return row do not match
 
914
DETAIL:  Returned type integer at ordinal position 2, but query expects numeric.
 
915
create or replace function foobar(out integer, out numeric) as
 
916
$$ select (1, 2.1, 3) $$ language sql;
 
917
select * from foobar();  -- fail
 
918
ERROR:  function return row and query-specified return row do not match
 
919
DETAIL:  Returned row contains 3 attributes, but query expects 2.
 
920
drop function foobar();