1 SELECT name, setting FROM pg_settings WHERE name LIKE 'enable%';
3 -------------------+---------
15 CREATE TABLE foo2(fooid int, f2 int);
16 INSERT INTO foo2 VALUES(1, 11);
17 INSERT INTO foo2 VALUES(2, 22);
18 INSERT INTO foo2 VALUES(1, 111);
19 CREATE FUNCTION foot(int) returns setof foo2 as 'SELECT * FROM foo2 WHERE fooid = $1;' LANGUAGE SQL;
20 -- supposed to fail with ERROR
21 select * from foo2, foot(foo2.fooid) z where foo2.f2 = z.f2;
22 ERROR: function expression in FROM cannot refer to other relations of same query level
23 -- function in subselect
24 select * from foo2 where f2 in (select f2 from foot(foo2.fooid) z where z.fooid = foo2.fooid) ORDER BY 1,2;
32 -- function in subselect
33 select * from foo2 where f2 in (select f2 from foot(1) z where z.fooid = foo2.fooid) ORDER BY 1,2;
40 -- function in subselect
41 select * from foo2 where f2 in (select f2 from foot(foo2.fooid) z where z.fooid = 1) ORDER BY 1,2;
49 select foot.fooid, foot.f2 from foot(sin(pi()/2)::int) ORDER BY 1,2;
56 CREATE TABLE foo (fooid int, foosubid int, fooname text, primary key(fooid,foosubid));
57 NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "foo_pkey" for table "foo"
58 INSERT INTO foo VALUES(1,1,'Joe');
59 INSERT INTO foo VALUES(1,2,'Ed');
60 INSERT INTO foo VALUES(2,1,'Mary');
61 -- sql, proretset = f, prorettype = b
62 CREATE FUNCTION getfoo(int) RETURNS int AS 'SELECT $1;' LANGUAGE SQL;
63 SELECT * FROM getfoo(1) AS t1;
69 CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);
70 SELECT * FROM vw_getfoo;
76 -- sql, proretset = t, prorettype = b
78 DROP FUNCTION getfoo(int);
79 CREATE FUNCTION getfoo(int) RETURNS setof int AS 'SELECT fooid FROM foo WHERE fooid = $1;' LANGUAGE SQL;
80 SELECT * FROM getfoo(1) AS t1;
87 CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);
88 SELECT * FROM vw_getfoo;
95 -- sql, proretset = t, prorettype = b
97 DROP FUNCTION getfoo(int);
98 CREATE FUNCTION getfoo(int) RETURNS setof text AS 'SELECT fooname FROM foo WHERE fooid = $1;' LANGUAGE SQL;
99 SELECT * FROM getfoo(1) AS t1;
106 CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);
107 SELECT * FROM vw_getfoo;
114 -- sql, proretset = f, prorettype = c
116 DROP FUNCTION getfoo(int);
117 CREATE FUNCTION getfoo(int) RETURNS foo AS 'SELECT * FROM foo WHERE fooid = $1;' LANGUAGE SQL;
118 SELECT * FROM getfoo(1) AS t1;
119 fooid | foosubid | fooname
120 -------+----------+---------
124 CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);
125 SELECT * FROM vw_getfoo;
126 fooid | foosubid | fooname
127 -------+----------+---------
131 -- sql, proretset = t, prorettype = c
133 DROP FUNCTION getfoo(int);
134 CREATE FUNCTION getfoo(int) RETURNS setof foo AS 'SELECT * FROM foo WHERE fooid = $1;' LANGUAGE SQL;
135 SELECT * FROM getfoo(1) AS t1;
136 fooid | foosubid | fooname
137 -------+----------+---------
142 CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);
143 SELECT * FROM vw_getfoo;
144 fooid | foosubid | fooname
145 -------+----------+---------
150 -- sql, proretset = f, prorettype = record
152 DROP FUNCTION getfoo(int);
153 CREATE FUNCTION getfoo(int) RETURNS RECORD AS 'SELECT * FROM foo WHERE fooid = $1;' LANGUAGE SQL;
154 SELECT * FROM getfoo(1) AS t1(fooid int, foosubid int, fooname text);
155 fooid | foosubid | fooname
156 -------+----------+---------
160 CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1) AS
161 (fooid int, foosubid int, fooname text);
162 SELECT * FROM vw_getfoo;
163 fooid | foosubid | fooname
164 -------+----------+---------
168 -- sql, proretset = t, prorettype = record
170 DROP FUNCTION getfoo(int);
171 CREATE FUNCTION getfoo(int) RETURNS setof record AS 'SELECT * FROM foo WHERE fooid = $1;' LANGUAGE SQL;
172 SELECT * FROM getfoo(1) AS t1(fooid int, foosubid int, fooname text);
173 fooid | foosubid | fooname
174 -------+----------+---------
179 CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1) AS
180 (fooid int, foosubid int, fooname text);
181 SELECT * FROM vw_getfoo;
182 fooid | foosubid | fooname
183 -------+----------+---------
188 -- plpgsql, proretset = f, prorettype = b
190 DROP FUNCTION getfoo(int);
191 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;
192 SELECT * FROM getfoo(1) AS t1;
198 CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);
199 SELECT * FROM vw_getfoo;
205 -- plpgsql, proretset = f, prorettype = c
207 DROP FUNCTION getfoo(int);
208 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;
209 SELECT * FROM getfoo(1) AS t1;
210 fooid | foosubid | fooname
211 -------+----------+---------
215 CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);
216 SELECT * FROM vw_getfoo;
217 fooid | foosubid | fooname
218 -------+----------+---------
223 DROP FUNCTION getfoo(int);
224 DROP FUNCTION foot(int);
228 CREATE TABLE foorescan (fooid int, foosubid int, fooname text, primary key(fooid,foosubid));
229 NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "foorescan_pkey" for table "foorescan"
230 INSERT INTO foorescan values(5000,1,'abc.5000.1');
231 INSERT INTO foorescan values(5001,1,'abc.5001.1');
232 INSERT INTO foorescan values(5002,1,'abc.5002.1');
233 INSERT INTO foorescan values(5003,1,'abc.5003.1');
234 INSERT INTO foorescan values(5004,1,'abc.5004.1');
235 INSERT INTO foorescan values(5005,1,'abc.5005.1');
236 INSERT INTO foorescan values(5006,1,'abc.5006.1');
237 INSERT INTO foorescan values(5007,1,'abc.5007.1');
238 INSERT INTO foorescan values(5008,1,'abc.5008.1');
239 INSERT INTO foorescan values(5009,1,'abc.5009.1');
240 INSERT INTO foorescan values(5000,2,'abc.5000.2');
241 INSERT INTO foorescan values(5001,2,'abc.5001.2');
242 INSERT INTO foorescan values(5002,2,'abc.5002.2');
243 INSERT INTO foorescan values(5003,2,'abc.5003.2');
244 INSERT INTO foorescan values(5004,2,'abc.5004.2');
245 INSERT INTO foorescan values(5005,2,'abc.5005.2');
246 INSERT INTO foorescan values(5006,2,'abc.5006.2');
247 INSERT INTO foorescan values(5007,2,'abc.5007.2');
248 INSERT INTO foorescan values(5008,2,'abc.5008.2');
249 INSERT INTO foorescan values(5009,2,'abc.5009.2');
250 INSERT INTO foorescan values(5000,3,'abc.5000.3');
251 INSERT INTO foorescan values(5001,3,'abc.5001.3');
252 INSERT INTO foorescan values(5002,3,'abc.5002.3');
253 INSERT INTO foorescan values(5003,3,'abc.5003.3');
254 INSERT INTO foorescan values(5004,3,'abc.5004.3');
255 INSERT INTO foorescan values(5005,3,'abc.5005.3');
256 INSERT INTO foorescan values(5006,3,'abc.5006.3');
257 INSERT INTO foorescan values(5007,3,'abc.5007.3');
258 INSERT INTO foorescan values(5008,3,'abc.5008.3');
259 INSERT INTO foorescan values(5009,3,'abc.5009.3');
260 INSERT INTO foorescan values(5000,4,'abc.5000.4');
261 INSERT INTO foorescan values(5001,4,'abc.5001.4');
262 INSERT INTO foorescan values(5002,4,'abc.5002.4');
263 INSERT INTO foorescan values(5003,4,'abc.5003.4');
264 INSERT INTO foorescan values(5004,4,'abc.5004.4');
265 INSERT INTO foorescan values(5005,4,'abc.5005.4');
266 INSERT INTO foorescan values(5006,4,'abc.5006.4');
267 INSERT INTO foorescan values(5007,4,'abc.5007.4');
268 INSERT INTO foorescan values(5008,4,'abc.5008.4');
269 INSERT INTO foorescan values(5009,4,'abc.5009.4');
270 INSERT INTO foorescan values(5000,5,'abc.5000.5');
271 INSERT INTO foorescan values(5001,5,'abc.5001.5');
272 INSERT INTO foorescan values(5002,5,'abc.5002.5');
273 INSERT INTO foorescan values(5003,5,'abc.5003.5');
274 INSERT INTO foorescan values(5004,5,'abc.5004.5');
275 INSERT INTO foorescan values(5005,5,'abc.5005.5');
276 INSERT INTO foorescan values(5006,5,'abc.5006.5');
277 INSERT INTO foorescan values(5007,5,'abc.5007.5');
278 INSERT INTO foorescan values(5008,5,'abc.5008.5');
279 INSERT INTO foorescan values(5009,5,'abc.5009.5');
280 CREATE FUNCTION foorescan(int,int) RETURNS setof foorescan AS 'SELECT * FROM foorescan WHERE fooid >= $1 and fooid < $2 ;' LANGUAGE SQL;
281 --invokes ExecFunctionReScan
282 SELECT * FROM foorescan f WHERE f.fooid IN (SELECT fooid FROM foorescan(5002,5004)) ORDER BY 1,2;
283 fooid | foosubid | fooname
284 -------+----------+------------
285 5002 | 1 | abc.5002.1
286 5002 | 2 | abc.5002.2
287 5002 | 3 | abc.5002.3
288 5002 | 4 | abc.5002.4
289 5002 | 5 | abc.5002.5
290 5003 | 1 | abc.5003.1
291 5003 | 2 | abc.5003.2
292 5003 | 3 | abc.5003.3
293 5003 | 4 | abc.5003.4
294 5003 | 5 | abc.5003.5
297 CREATE VIEW vw_foorescan AS SELECT * FROM foorescan(5002,5004);
298 --invokes ExecFunctionReScan
299 SELECT * FROM foorescan f WHERE f.fooid IN (SELECT fooid FROM vw_foorescan) ORDER BY 1,2;
300 fooid | foosubid | fooname
301 -------+----------+------------
302 5002 | 1 | abc.5002.1
303 5002 | 2 | abc.5002.2
304 5002 | 3 | abc.5002.3
305 5002 | 4 | abc.5002.4
306 5002 | 5 | abc.5002.5
307 5003 | 1 | abc.5003.1
308 5003 | 2 | abc.5003.2
309 5003 | 3 | abc.5003.3
310 5003 | 4 | abc.5003.4
311 5003 | 5 | abc.5003.5
314 CREATE TABLE barrescan (fooid int primary key);
315 NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "barrescan_pkey" for table "barrescan"
316 INSERT INTO barrescan values(5003);
317 INSERT INTO barrescan values(5004);
318 INSERT INTO barrescan values(5005);
319 INSERT INTO barrescan values(5006);
320 INSERT INTO barrescan values(5007);
321 INSERT INTO barrescan values(5008);
322 CREATE FUNCTION foorescan(int) RETURNS setof foorescan AS 'SELECT * FROM foorescan WHERE fooid = $1;' LANGUAGE SQL;
323 --invokes ExecFunctionReScan with chgParam != NULL
324 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;
325 fooid | foosubid | fooname
326 -------+----------+------------
327 5003 | 1 | abc.5003.1
328 5003 | 2 | abc.5003.2
329 5003 | 3 | abc.5003.3
330 5003 | 4 | abc.5003.4
331 5003 | 5 | abc.5003.5
332 5004 | 1 | abc.5004.1
333 5004 | 2 | abc.5004.2
334 5004 | 3 | abc.5004.3
335 5004 | 4 | abc.5004.4
336 5004 | 5 | abc.5004.5
337 5005 | 1 | abc.5005.1
338 5005 | 2 | abc.5005.2
339 5005 | 3 | abc.5005.3
340 5005 | 4 | abc.5005.4
341 5005 | 5 | abc.5005.5
342 5006 | 1 | abc.5006.1
343 5006 | 2 | abc.5006.2
344 5006 | 3 | abc.5006.3
345 5006 | 4 | abc.5006.4
346 5006 | 5 | abc.5006.5
347 5007 | 1 | abc.5007.1
348 5007 | 2 | abc.5007.2
349 5007 | 3 | abc.5007.3
350 5007 | 4 | abc.5007.4
351 5007 | 5 | abc.5007.5
352 5008 | 1 | abc.5008.1
353 5008 | 2 | abc.5008.2
354 5008 | 3 | abc.5008.3
355 5008 | 4 | abc.5008.4
356 5008 | 5 | abc.5008.5
359 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;
370 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;
371 SELECT * FROM fooview1 AS fv WHERE fv.fooid = 5004;
372 fooid | foosubid | fooname
373 -------+----------+------------
374 5004 | 1 | abc.5004.1
375 5004 | 2 | abc.5004.2
376 5004 | 3 | abc.5004.3
377 5004 | 4 | abc.5004.4
378 5004 | 5 | abc.5004.5
381 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;
382 SELECT * FROM fooview2 AS fv WHERE fv.maxsubid = 5;
393 DROP VIEW vw_foorescan;
396 DROP FUNCTION foorescan(int,int);
397 DROP FUNCTION foorescan(int);
398 DROP TABLE foorescan;
399 DROP TABLE barrescan;
401 -- Test cases involving OUT parameters
403 CREATE FUNCTION foo(in f1 int, out f2 int)
404 AS 'select $1+1' LANGUAGE sql;
411 SELECT * FROM foo(42);
417 SELECT * FROM foo(42) AS p(x);
423 -- explicit spec of return type is OK
424 CREATE OR REPLACE FUNCTION foo(in f1 int, out f2 int) RETURNS int
425 AS 'select $1+1' LANGUAGE sql;
426 -- error, wrong result type
427 CREATE OR REPLACE FUNCTION foo(in f1 int, out f2 int) RETURNS float
428 AS 'select $1+1' LANGUAGE sql;
429 ERROR: function result type must be integer because of OUT parameters
430 -- with multiple OUT params you must get a RECORD result
431 CREATE OR REPLACE FUNCTION foo(in f1 int, out f2 int, out f3 text) RETURNS int
432 AS 'select $1+1' LANGUAGE sql;
433 ERROR: function result type must be record because of OUT parameters
434 CREATE OR REPLACE FUNCTION foo(in f1 int, out f2 int, out f3 text)
436 AS 'select $1+1' LANGUAGE sql;
437 ERROR: cannot change return type of existing function
438 HINT: Use DROP FUNCTION first.
439 CREATE OR REPLACE FUNCTION foor(in f1 int, out f2 int, out text)
440 AS $$select $1-1, $1::text || 'z'$$ LANGUAGE sql;
441 SELECT f1, foor(f1) FROM int4_tbl;
443 -------------+----------------------------
445 123456 | (123455,123456z)
446 -123456 | (-123457,-123456z)
447 2147483647 | (2147483646,2147483647z)
448 -2147483647 | (-2147483648,-2147483647z)
451 SELECT * FROM foor(42);
457 SELECT * FROM foor(42) AS p(a,b);
463 CREATE OR REPLACE FUNCTION foob(in f1 int, inout f2 int, out text)
464 AS $$select $2-1, $1::text || 'z'$$ LANGUAGE sql;
465 SELECT f1, foob(f1, f1/2) FROM int4_tbl;
467 -------------+----------------------------
469 123456 | (61727,123456z)
470 -123456 | (-61729,-123456z)
471 2147483647 | (1073741822,2147483647z)
472 -2147483647 | (-1073741824,-2147483647z)
475 SELECT * FROM foob(42, 99);
481 SELECT * FROM foob(42, 99) AS p(a,b);
487 -- Can reference function with or without OUT params for DROP, etc
488 DROP FUNCTION foo(int);
489 DROP FUNCTION foor(in f2 int, out f1 int, out text);
490 DROP FUNCTION foob(in f1 int, inout f2 int);
492 -- For my next trick, polymorphic OUT parameters
494 CREATE FUNCTION dup (f1 anyelement, f2 out anyelement, f3 out anyarray)
495 AS 'select $1, array[$1,$1]' LANGUAGE sql;
502 SELECT dup('xyz'); -- fails
503 ERROR: could not determine polymorphic type because input has type "unknown"
504 SELECT dup('xyz'::text);
510 SELECT * FROM dup('xyz'::text);
516 -- equivalent specification
517 CREATE OR REPLACE FUNCTION dup (inout f2 anyelement, out f3 anyarray)
518 AS 'select $1, array[$1,$1]' LANGUAGE sql;
525 DROP FUNCTION dup(anyelement);
526 -- fails, no way to deduce outputs
527 CREATE FUNCTION bad (f1 int, out f2 anyelement, out f3 anyarray)
528 AS 'select $1, array[$1,$1]' LANGUAGE sql;
529 ERROR: cannot determine result data type
530 DETAIL: A function returning a polymorphic type must have at least one polymorphic argument.
534 CREATE OR REPLACE FUNCTION foo()
536 AS $$ SELECT a FROM generate_series(1,5) a(a) $$ LANGUAGE sql;
548 CREATE OR REPLACE FUNCTION foo(int)
549 RETURNS TABLE(a int, b int)
551 FROM generate_series(1,$1) a(a),
552 generate_series(1,$1) b(b) $$ LANGUAGE sql;
553 SELECT * FROM foo(3);
567 DROP FUNCTION foo(int);