1
SELECT name, setting FROM pg_settings WHERE name LIKE 'enable%';
3
------------------+---------
14
CREATE TABLE foo2(fooid int, f2 int);
15
INSERT INTO foo2 VALUES(1, 11);
16
INSERT INTO foo2 VALUES(2, 22);
17
INSERT INTO foo2 VALUES(1, 111);
18
CREATE FUNCTION foot(int) returns setof foo2 as 'SELECT * FROM foo2 WHERE fooid = $1;' LANGUAGE SQL;
19
-- supposed to fail with ERROR
20
select * from foo2, foot(foo2.fooid) z where foo2.f2 = z.f2;
21
ERROR: function expression in FROM may not refer to other relations of same query level
22
-- function in subselect
23
select * from foo2 where f2 in (select f2 from foot(foo2.fooid) z where z.fooid = foo2.fooid) ORDER BY 1,2;
31
-- function in subselect
32
select * from foo2 where f2 in (select f2 from foot(1) z where z.fooid = foo2.fooid) ORDER BY 1,2;
39
-- function in subselect
40
select * from foo2 where f2 in (select f2 from foot(foo2.fooid) z where z.fooid = 1) ORDER BY 1,2;
48
select foot.fooid, foot.f2 from foot(sin(pi()/2)::int) ORDER BY 1,2;
55
CREATE TABLE foo (fooid int, foosubid int, fooname text, primary key(fooid,foosubid));
56
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "foo_pkey" for table "foo"
57
INSERT INTO foo VALUES(1,1,'Joe');
58
INSERT INTO foo VALUES(1,2,'Ed');
59
INSERT INTO foo VALUES(2,1,'Mary');
60
-- sql, proretset = f, prorettype = b
61
CREATE FUNCTION getfoo(int) RETURNS int AS 'SELECT $1;' LANGUAGE SQL;
62
SELECT * FROM getfoo(1) AS t1;
68
CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);
69
SELECT * FROM vw_getfoo;
75
-- sql, proretset = t, prorettype = b
77
DROP FUNCTION getfoo(int);
78
CREATE FUNCTION getfoo(int) RETURNS setof int AS 'SELECT fooid FROM foo WHERE fooid = $1;' LANGUAGE SQL;
79
SELECT * FROM getfoo(1) AS t1;
86
CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);
87
SELECT * FROM vw_getfoo;
94
-- sql, proretset = t, prorettype = b
96
DROP FUNCTION getfoo(int);
97
CREATE FUNCTION getfoo(int) RETURNS setof text AS 'SELECT fooname FROM foo WHERE fooid = $1;' LANGUAGE SQL;
98
SELECT * FROM getfoo(1) AS t1;
105
CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);
106
SELECT * FROM vw_getfoo;
113
-- sql, proretset = f, prorettype = c
115
DROP FUNCTION getfoo(int);
116
CREATE FUNCTION getfoo(int) RETURNS foo AS 'SELECT * FROM foo WHERE fooid = $1;' LANGUAGE SQL;
117
SELECT * FROM getfoo(1) AS t1;
118
fooid | foosubid | fooname
119
-------+----------+---------
123
CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);
124
SELECT * FROM vw_getfoo;
125
fooid | foosubid | fooname
126
-------+----------+---------
130
-- sql, proretset = t, prorettype = c
132
DROP FUNCTION getfoo(int);
133
CREATE FUNCTION getfoo(int) RETURNS setof foo AS 'SELECT * FROM foo WHERE fooid = $1;' LANGUAGE SQL;
134
SELECT * FROM getfoo(1) AS t1;
135
fooid | foosubid | fooname
136
-------+----------+---------
141
CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);
142
SELECT * FROM vw_getfoo;
143
fooid | foosubid | fooname
144
-------+----------+---------
149
-- sql, proretset = f, prorettype = record
151
DROP FUNCTION getfoo(int);
152
CREATE FUNCTION getfoo(int) RETURNS RECORD AS 'SELECT * FROM foo WHERE fooid = $1;' LANGUAGE SQL;
153
SELECT * FROM getfoo(1) AS t1(fooid int, foosubid int, fooname text);
154
fooid | foosubid | fooname
155
-------+----------+---------
159
CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1) AS
160
(fooid int, foosubid int, fooname text);
161
SELECT * FROM vw_getfoo;
162
fooid | foosubid | fooname
163
-------+----------+---------
167
-- sql, proretset = t, prorettype = record
169
DROP FUNCTION getfoo(int);
170
CREATE FUNCTION getfoo(int) RETURNS setof record AS 'SELECT * FROM foo WHERE fooid = $1;' LANGUAGE SQL;
171
SELECT * FROM getfoo(1) AS t1(fooid int, foosubid int, fooname text);
172
fooid | foosubid | fooname
173
-------+----------+---------
178
CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1) AS
179
(fooid int, foosubid int, fooname text);
180
SELECT * FROM vw_getfoo;
181
fooid | foosubid | fooname
182
-------+----------+---------
187
-- plpgsql, proretset = f, prorettype = b
189
DROP FUNCTION getfoo(int);
190
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';
191
SELECT * FROM getfoo(1) AS t1;
197
CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);
198
SELECT * FROM vw_getfoo;
204
-- plpgsql, proretset = f, prorettype = c
206
DROP FUNCTION getfoo(int);
207
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';
208
SELECT * FROM getfoo(1) AS t1;
209
fooid | foosubid | fooname
210
-------+----------+---------
214
CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);
215
SELECT * FROM vw_getfoo;
216
fooid | foosubid | fooname
217
-------+----------+---------
222
DROP FUNCTION getfoo(int);
223
DROP FUNCTION foot(int);
227
CREATE TABLE foorescan (fooid int, foosubid int, fooname text, primary key(fooid,foosubid));
228
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "foorescan_pkey" for table "foorescan"
229
INSERT INTO foorescan values(5000,1,'abc.5000.1');
230
INSERT INTO foorescan values(5001,1,'abc.5001.1');
231
INSERT INTO foorescan values(5002,1,'abc.5002.1');
232
INSERT INTO foorescan values(5003,1,'abc.5003.1');
233
INSERT INTO foorescan values(5004,1,'abc.5004.1');
234
INSERT INTO foorescan values(5005,1,'abc.5005.1');
235
INSERT INTO foorescan values(5006,1,'abc.5006.1');
236
INSERT INTO foorescan values(5007,1,'abc.5007.1');
237
INSERT INTO foorescan values(5008,1,'abc.5008.1');
238
INSERT INTO foorescan values(5009,1,'abc.5009.1');
239
INSERT INTO foorescan values(5000,2,'abc.5000.2');
240
INSERT INTO foorescan values(5001,2,'abc.5001.2');
241
INSERT INTO foorescan values(5002,2,'abc.5002.2');
242
INSERT INTO foorescan values(5003,2,'abc.5003.2');
243
INSERT INTO foorescan values(5004,2,'abc.5004.2');
244
INSERT INTO foorescan values(5005,2,'abc.5005.2');
245
INSERT INTO foorescan values(5006,2,'abc.5006.2');
246
INSERT INTO foorescan values(5007,2,'abc.5007.2');
247
INSERT INTO foorescan values(5008,2,'abc.5008.2');
248
INSERT INTO foorescan values(5009,2,'abc.5009.2');
249
INSERT INTO foorescan values(5000,3,'abc.5000.3');
250
INSERT INTO foorescan values(5001,3,'abc.5001.3');
251
INSERT INTO foorescan values(5002,3,'abc.5002.3');
252
INSERT INTO foorescan values(5003,3,'abc.5003.3');
253
INSERT INTO foorescan values(5004,3,'abc.5004.3');
254
INSERT INTO foorescan values(5005,3,'abc.5005.3');
255
INSERT INTO foorescan values(5006,3,'abc.5006.3');
256
INSERT INTO foorescan values(5007,3,'abc.5007.3');
257
INSERT INTO foorescan values(5008,3,'abc.5008.3');
258
INSERT INTO foorescan values(5009,3,'abc.5009.3');
259
INSERT INTO foorescan values(5000,4,'abc.5000.4');
260
INSERT INTO foorescan values(5001,4,'abc.5001.4');
261
INSERT INTO foorescan values(5002,4,'abc.5002.4');
262
INSERT INTO foorescan values(5003,4,'abc.5003.4');
263
INSERT INTO foorescan values(5004,4,'abc.5004.4');
264
INSERT INTO foorescan values(5005,4,'abc.5005.4');
265
INSERT INTO foorescan values(5006,4,'abc.5006.4');
266
INSERT INTO foorescan values(5007,4,'abc.5007.4');
267
INSERT INTO foorescan values(5008,4,'abc.5008.4');
268
INSERT INTO foorescan values(5009,4,'abc.5009.4');
269
INSERT INTO foorescan values(5000,5,'abc.5000.5');
270
INSERT INTO foorescan values(5001,5,'abc.5001.5');
271
INSERT INTO foorescan values(5002,5,'abc.5002.5');
272
INSERT INTO foorescan values(5003,5,'abc.5003.5');
273
INSERT INTO foorescan values(5004,5,'abc.5004.5');
274
INSERT INTO foorescan values(5005,5,'abc.5005.5');
275
INSERT INTO foorescan values(5006,5,'abc.5006.5');
276
INSERT INTO foorescan values(5007,5,'abc.5007.5');
277
INSERT INTO foorescan values(5008,5,'abc.5008.5');
278
INSERT INTO foorescan values(5009,5,'abc.5009.5');
279
CREATE FUNCTION foorescan(int,int) RETURNS setof foorescan AS 'SELECT * FROM foorescan WHERE fooid >= $1 and fooid < $2 ;' LANGUAGE SQL;
280
--invokes ExecFunctionReScan
281
SELECT * FROM foorescan f WHERE f.fooid IN (SELECT fooid FROM foorescan(5002,5004)) ORDER BY 1,2;
282
fooid | foosubid | fooname
283
-------+----------+------------
284
5002 | 1 | abc.5002.1
285
5002 | 2 | abc.5002.2
286
5002 | 3 | abc.5002.3
287
5002 | 4 | abc.5002.4
288
5002 | 5 | abc.5002.5
289
5003 | 1 | abc.5003.1
290
5003 | 2 | abc.5003.2
291
5003 | 3 | abc.5003.3
292
5003 | 4 | abc.5003.4
293
5003 | 5 | abc.5003.5
296
CREATE VIEW vw_foorescan AS SELECT * FROM foorescan(5002,5004);
297
--invokes ExecFunctionReScan
298
SELECT * FROM foorescan f WHERE f.fooid IN (SELECT fooid FROM vw_foorescan) ORDER BY 1,2;
299
fooid | foosubid | fooname
300
-------+----------+------------
301
5002 | 1 | abc.5002.1
302
5002 | 2 | abc.5002.2
303
5002 | 3 | abc.5002.3
304
5002 | 4 | abc.5002.4
305
5002 | 5 | abc.5002.5
306
5003 | 1 | abc.5003.1
307
5003 | 2 | abc.5003.2
308
5003 | 3 | abc.5003.3
309
5003 | 4 | abc.5003.4
310
5003 | 5 | abc.5003.5
313
CREATE TABLE barrescan (fooid int primary key);
314
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "barrescan_pkey" for table "barrescan"
315
INSERT INTO barrescan values(5003);
316
INSERT INTO barrescan values(5004);
317
INSERT INTO barrescan values(5005);
318
INSERT INTO barrescan values(5006);
319
INSERT INTO barrescan values(5007);
320
INSERT INTO barrescan values(5008);
321
CREATE FUNCTION foorescan(int) RETURNS setof foorescan AS 'SELECT * FROM foorescan WHERE fooid = $1;' LANGUAGE SQL;
322
--invokes ExecFunctionReScan with chgParam != NULL
323
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;
324
fooid | foosubid | fooname
325
-------+----------+------------
326
5003 | 1 | abc.5003.1
327
5003 | 2 | abc.5003.2
328
5003 | 3 | abc.5003.3
329
5003 | 4 | abc.5003.4
330
5003 | 5 | abc.5003.5
331
5004 | 1 | abc.5004.1
332
5004 | 2 | abc.5004.2
333
5004 | 3 | abc.5004.3
334
5004 | 4 | abc.5004.4
335
5004 | 5 | abc.5004.5
336
5005 | 1 | abc.5005.1
337
5005 | 2 | abc.5005.2
338
5005 | 3 | abc.5005.3
339
5005 | 4 | abc.5005.4
340
5005 | 5 | abc.5005.5
341
5006 | 1 | abc.5006.1
342
5006 | 2 | abc.5006.2
343
5006 | 3 | abc.5006.3
344
5006 | 4 | abc.5006.4
345
5006 | 5 | abc.5006.5
346
5007 | 1 | abc.5007.1
347
5007 | 2 | abc.5007.2
348
5007 | 3 | abc.5007.3
349
5007 | 4 | abc.5007.4
350
5007 | 5 | abc.5007.5
351
5008 | 1 | abc.5008.1
352
5008 | 2 | abc.5008.2
353
5008 | 3 | abc.5008.3
354
5008 | 4 | abc.5008.4
355
5008 | 5 | abc.5008.5
358
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;
369
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;
370
SELECT * FROM fooview1 AS fv WHERE fv.fooid = 5004;
371
fooid | foosubid | fooname
372
-------+----------+------------
373
5004 | 1 | abc.5004.1
374
5004 | 2 | abc.5004.2
375
5004 | 3 | abc.5004.3
376
5004 | 4 | abc.5004.4
377
5004 | 5 | abc.5004.5
380
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;
381
SELECT * FROM fooview2 AS fv WHERE fv.maxsubid = 5;
392
DROP VIEW vw_foorescan;
395
DROP FUNCTION foorescan(int,int);
396
DROP FUNCTION foorescan(int);
397
DROP TABLE foorescan;
398
DROP TABLE barrescan;