| blob | 397a8b35d6d5c2fb1ff900ef6f407fb60e45484b |
1 CREATE TABLE rngfunc2(rngfuncid int, f2 int);
2 INSERT INTO rngfunc2 VALUES(1, 11);
3 INSERT INTO rngfunc2 VALUES(2, 22);
4 INSERT INTO rngfunc2 VALUES(1, 111);
5 CREATE FUNCTION rngfunct(int) returns setof rngfunc2 as 'SELECT * FROM rngfunc2 WHERE rngfuncid = $1 ORDER BY f2;' LANGUAGE SQL;
6 -- function with ORDINALITY
7 select * from rngfunct(1) with ordinality as z(a,b,ord);
8 a | b | ord
9 ---+-----+-----
10 1 | 11 | 1
11 1 | 111 | 2
12 (2 rows)
14 select * from rngfunct(1) with ordinality as z(a,b,ord) where b > 100; -- ordinal 2, not 1
15 a | b | ord
16 ---+-----+-----
17 1 | 111 | 2
18 (1 row)
20 -- ordinality vs. column names and types
21 select a,b,ord from rngfunct(1) with ordinality as z(a,b,ord);
22 a | b | ord
23 ---+-----+-----
24 1 | 11 | 1
25 1 | 111 | 2
26 (2 rows)
28 select a,ord from unnest(array['a','b']) with ordinality as z(a,ord);
29 a | ord
30 ---+-----
31 a | 1
32 b | 2
33 (2 rows)
35 select * from unnest(array['a','b']) with ordinality as z(a,ord);
36 a | ord
37 ---+-----
38 a | 1
39 b | 2
40 (2 rows)
42 select a,ord from unnest(array[1.0::float8]) with ordinality as z(a,ord);
43 a | ord
44 ---+-----
45 1 | 1
46 (1 row)
48 select * from unnest(array[1.0::float8]) with ordinality as z(a,ord);
49 a | ord
50 ---+-----
51 1 | 1
52 (1 row)
54 select row_to_json(s.*) from generate_series(11,14) with ordinality s;
55 row_to_json
56 -------------------------
57 {"s":11,"ordinality":1}
58 {"s":12,"ordinality":2}
59 {"s":13,"ordinality":3}
60 {"s":14,"ordinality":4}
61 (4 rows)
63 -- ordinality vs. views
64 create temporary view vw_ord as select * from (values (1)) v(n) join rngfunct(1) with ordinality as z(a,b,ord) on (n=ord);
65 select * from vw_ord;
66 n | a | b | ord
67 ---+---+----+-----
68 1 | 1 | 11 | 1
69 (1 row)
71 select definition from pg_views where viewname='vw_ord';
72 definition
73 -------------------------------------------------------------------------
74 SELECT v.n, +
75 z.a, +
76 z.b, +
77 z.ord +
78 FROM (( VALUES (1)) v(n) +
79 JOIN rngfunct(1) WITH ORDINALITY z(a, b, ord) ON ((v.n = z.ord)));
80 (1 row)
82 drop view vw_ord;
83 -- multiple functions
84 select * from rows from(rngfunct(1),rngfunct(2)) with ordinality as z(a,b,c,d,ord);
85 a | b | c | d | ord
86 ---+-----+---+----+-----
87 1 | 11 | 2 | 22 | 1
88 1 | 111 | | | 2
89 (2 rows)
91 create temporary view vw_ord as select * from (values (1)) v(n) join rows from(rngfunct(1),rngfunct(2)) with ordinality as z(a,b,c,d,ord) on (n=ord);
92 select * from vw_ord;
93 n | a | b | c | d | ord
94 ---+---+----+---+----+-----
95 1 | 1 | 11 | 2 | 22 | 1
96 (1 row)
98 select definition from pg_views where viewname='vw_ord';
99 definition
100 -------------------------------------------------------------------------------------------------------
101 SELECT v.n, +
102 z.a, +
103 z.b, +
104 z.c, +
105 z.d, +
106 z.ord +
107 FROM (( VALUES (1)) v(n) +
108 JOIN ROWS FROM(rngfunct(1), rngfunct(2)) WITH ORDINALITY z(a, b, c, d, ord) ON ((v.n = z.ord)));
109 (1 row)
111 drop view vw_ord;
112 -- expansions of unnest()
113 select * from unnest(array[10,20],array['foo','bar'],array[1.0]);
114 unnest | unnest | unnest
115 --------+--------+--------
116 10 | foo | 1.0
117 20 | bar |
118 (2 rows)
120 select * from unnest(array[10,20],array['foo','bar'],array[1.0]) with ordinality as z(a,b,c,ord);
121 a | b | c | ord
122 ----+-----+-----+-----
123 10 | foo | 1.0 | 1
124 20 | bar | | 2
125 (2 rows)
127 select * from rows from(unnest(array[10,20],array['foo','bar'],array[1.0])) with ordinality as z(a,b,c,ord);
128 a | b | c | ord
129 ----+-----+-----+-----
130 10 | foo | 1.0 | 1
131 20 | bar | | 2
132 (2 rows)
134 select * from rows from(unnest(array[10,20],array['foo','bar']), generate_series(101,102)) with ordinality as z(a,b,c,ord);
135 a | b | c | ord
136 ----+-----+-----+-----
137 10 | foo | 101 | 1
138 20 | bar | 102 | 2
139 (2 rows)
141 create temporary view vw_ord as select * from unnest(array[10,20],array['foo','bar'],array[1.0]) as z(a,b,c);
142 select * from vw_ord;
143 a | b | c
144 ----+-----+-----
145 10 | foo | 1.0
146 20 | bar |
147 (2 rows)
149 select definition from pg_views where viewname='vw_ord';
150 definition
151 ----------------------------------------------------------------------------------------
152 SELECT a, +
153 b, +
154 c +
155 FROM UNNEST(ARRAY[10, 20], ARRAY['foo'::text, 'bar'::text], ARRAY[1.0]) z(a, b, c);
156 (1 row)
158 drop view vw_ord;
159 create temporary view vw_ord as select * from rows from(unnest(array[10,20],array['foo','bar'],array[1.0])) as z(a,b,c);
160 select * from vw_ord;
161 a | b | c
162 ----+-----+-----
163 10 | foo | 1.0
164 20 | bar |
165 (2 rows)
167 select definition from pg_views where viewname='vw_ord';
168 definition
169 ----------------------------------------------------------------------------------------
170 SELECT a, +
171 b, +
172 c +
173 FROM UNNEST(ARRAY[10, 20], ARRAY['foo'::text, 'bar'::text], ARRAY[1.0]) z(a, b, c);
174 (1 row)
176 drop view vw_ord;
177 create temporary view vw_ord as select * from rows from(unnest(array[10,20],array['foo','bar']), generate_series(1,2)) as z(a,b,c);
178 select * from vw_ord;
179 a | b | c
180 ----+-----+---
181 10 | foo | 1
182 20 | bar | 2
183 (2 rows)
185 select definition from pg_views where viewname='vw_ord';
186 definition
187 ----------------------------------------------------------------------------------------------------------------------
188 SELECT a, +
189 b, +
190 c +
191 FROM ROWS FROM(unnest(ARRAY[10, 20]), unnest(ARRAY['foo'::text, 'bar'::text]), generate_series(1, 2)) z(a, b, c);
192 (1 row)
194 drop view vw_ord;
195 -- ordinality and multiple functions vs. rewind and reverse scan
196 begin;
197 declare rf_cur scroll cursor for select * from rows from(generate_series(1,5),generate_series(1,2)) with ordinality as g(i,j,o);
198 fetch all from rf_cur;
199 i | j | o
200 ---+---+---
201 1 | 1 | 1
202 2 | 2 | 2
203 3 | | 3
204 4 | | 4
205 5 | | 5
206 (5 rows)
208 fetch backward all from rf_cur;
209 i | j | o
210 ---+---+---
211 5 | | 5
212 4 | | 4
213 3 | | 3
214 2 | 2 | 2
215 1 | 1 | 1
216 (5 rows)
218 fetch all from rf_cur;
219 i | j | o
220 ---+---+---
221 1 | 1 | 1
222 2 | 2 | 2
223 3 | | 3
224 4 | | 4
225 5 | | 5
226 (5 rows)
228 fetch next from rf_cur;
229 i | j | o
230 ---+---+---
231 (0 rows)
233 fetch next from rf_cur;
234 i | j | o
235 ---+---+---
236 (0 rows)
238 fetch prior from rf_cur;
239 i | j | o
240 ---+---+---
241 5 | | 5
242 (1 row)
244 fetch absolute 1 from rf_cur;
245 i | j | o
246 ---+---+---
247 1 | 1 | 1
248 (1 row)
250 fetch next from rf_cur;
251 i | j | o
252 ---+---+---
253 2 | 2 | 2
254 (1 row)
256 fetch next from rf_cur;
257 i | j | o
258 ---+---+---
259 3 | | 3
260 (1 row)
262 fetch next from rf_cur;
263 i | j | o
264 ---+---+---
265 4 | | 4
266 (1 row)
268 fetch prior from rf_cur;
269 i | j | o
270 ---+---+---
271 3 | | 3
272 (1 row)
274 fetch prior from rf_cur;
275 i | j | o
276 ---+---+---
277 2 | 2 | 2
278 (1 row)
280 fetch prior from rf_cur;
281 i | j | o
282 ---+---+---
283 1 | 1 | 1
284 (1 row)
286 commit;
287 -- function with implicit LATERAL
288 select * from rngfunc2, rngfunct(rngfunc2.rngfuncid) z where rngfunc2.f2 = z.f2;
289 rngfuncid | f2 | rngfuncid | f2
290 -----------+-----+-----------+-----
291 1 | 11 | 1 | 11
292 2 | 22 | 2 | 22
293 1 | 111 | 1 | 111
294 (3 rows)
296 -- function with implicit LATERAL and explicit ORDINALITY
297 select * from rngfunc2, rngfunct(rngfunc2.rngfuncid) with ordinality as z(rngfuncid,f2,ord) where rngfunc2.f2 = z.f2;
298 rngfuncid | f2 | rngfuncid | f2 | ord
299 -----------+-----+-----------+-----+-----
300 1 | 11 | 1 | 11 | 1
301 2 | 22 | 2 | 22 | 1
302 1 | 111 | 1 | 111 | 2
303 (3 rows)
305 -- function in subselect
306 select * from rngfunc2 where f2 in (select f2 from rngfunct(rngfunc2.rngfuncid) z where z.rngfuncid = rngfunc2.rngfuncid) ORDER BY 1,2;
307 rngfuncid | f2
308 -----------+-----
309 1 | 11
310 1 | 111
311 2 | 22
312 (3 rows)
314 -- function in subselect
315 select * from rngfunc2 where f2 in (select f2 from rngfunct(1) z where z.rngfuncid = rngfunc2.rngfuncid) ORDER BY 1,2;
316 rngfuncid | f2
317 -----------+-----
318 1 | 11
319 1 | 111
320 (2 rows)
322 -- function in subselect
323 select * from rngfunc2 where f2 in (select f2 from rngfunct(rngfunc2.rngfuncid) z where z.rngfuncid = 1) ORDER BY 1,2;
324 rngfuncid | f2
325 -----------+-----
326 1 | 11
327 1 | 111
328 (2 rows)
330 -- nested functions
331 select rngfunct.rngfuncid, rngfunct.f2 from rngfunct(sin(pi()/2)::int) ORDER BY 1,2;
332 rngfuncid | f2
333 -----------+-----
334 1 | 11
335 1 | 111
336 (2 rows)
338 CREATE TABLE rngfunc (rngfuncid int, rngfuncsubid int, rngfuncname text, primary key(rngfuncid,rngfuncsubid));
339 INSERT INTO rngfunc VALUES(1,1,'Joe');
340 INSERT INTO rngfunc VALUES(1,2,'Ed');
341 INSERT INTO rngfunc VALUES(2,1,'Mary');
342 -- sql, proretset = f, prorettype = b
343 CREATE FUNCTION getrngfunc1(int) RETURNS int AS 'SELECT $1;' LANGUAGE SQL;
344 SELECT * FROM getrngfunc1(1) AS t1;
345 t1
346 ----
347 1
348 (1 row)
350 SELECT * FROM getrngfunc1(1) WITH ORDINALITY AS t1(v,o);
351 v | o
352 ---+---
353 1 | 1
354 (1 row)
356 CREATE VIEW vw_getrngfunc AS SELECT * FROM getrngfunc1(1);
357 SELECT * FROM vw_getrngfunc;
358 getrngfunc1
359 -------------
360 1
361 (1 row)
363 DROP VIEW vw_getrngfunc;
364 CREATE VIEW vw_getrngfunc AS SELECT * FROM getrngfunc1(1) WITH ORDINALITY as t1(v,o);
365 SELECT * FROM vw_getrngfunc;
366 v | o
367 ---+---
368 1 | 1
369 (1 row)
371 DROP VIEW vw_getrngfunc;
372 -- sql, proretset = t, prorettype = b
373 CREATE FUNCTION getrngfunc2(int) RETURNS setof int AS 'SELECT rngfuncid FROM rngfunc WHERE rngfuncid = $1;' LANGUAGE SQL;
374 SELECT * FROM getrngfunc2(1) AS t1;
375 t1
376 ----
377 1
378 1
379 (2 rows)
381 SELECT * FROM getrngfunc2(1) WITH ORDINALITY AS t1(v,o);
382 v | o
383 ---+---
384 1 | 1
385 1 | 2
386 (2 rows)
388 CREATE VIEW vw_getrngfunc AS SELECT * FROM getrngfunc2(1);
389 SELECT * FROM vw_getrngfunc;
390 getrngfunc2
391 -------------
392 1
393 1
394 (2 rows)
396 DROP VIEW vw_getrngfunc;
397 CREATE VIEW vw_getrngfunc AS SELECT * FROM getrngfunc2(1) WITH ORDINALITY AS t1(v,o);
398 SELECT * FROM vw_getrngfunc;
399 v | o
400 ---+---
401 1 | 1
402 1 | 2
403 (2 rows)
405 DROP VIEW vw_getrngfunc;
406 -- sql, proretset = t, prorettype = b
407 CREATE FUNCTION getrngfunc3(int) RETURNS setof text AS 'SELECT rngfuncname FROM rngfunc WHERE rngfuncid = $1;' LANGUAGE SQL;
408 SELECT * FROM getrngfunc3(1) AS t1;
409 t1
410 -----
411 Joe
412 Ed
413 (2 rows)
415 SELECT * FROM getrngfunc3(1) WITH ORDINALITY AS t1(v,o);
416 v | o
417 -----+---
418 Joe | 1
419 Ed | 2
420 (2 rows)
422 CREATE VIEW vw_getrngfunc AS SELECT * FROM getrngfunc3(1);
423 SELECT * FROM vw_getrngfunc;
424 getrngfunc3
425 -------------
426 Joe
427 Ed
428 (2 rows)
430 DROP VIEW vw_getrngfunc;
431 CREATE VIEW vw_getrngfunc AS SELECT * FROM getrngfunc3(1) WITH ORDINALITY AS t1(v,o);
432 SELECT * FROM vw_getrngfunc;
433 v | o
434 -----+---
435 Joe | 1
436 Ed | 2
437 (2 rows)
439 DROP VIEW vw_getrngfunc;
440 -- sql, proretset = f, prorettype = c
441 CREATE FUNCTION getrngfunc4(int) RETURNS rngfunc AS 'SELECT * FROM rngfunc WHERE rngfuncid = $1;' LANGUAGE SQL;
442 SELECT * FROM getrngfunc4(1) AS t1;
443 rngfuncid | rngfuncsubid | rngfuncname
444 -----------+--------------+-------------
445 1 | 1 | Joe
446 (1 row)
448 SELECT * FROM getrngfunc4(1) WITH ORDINALITY AS t1(a,b,c,o);
449 a | b | c | o
450 ---+---+-----+---
451 1 | 1 | Joe | 1
452 (1 row)
454 CREATE VIEW vw_getrngfunc AS SELECT * FROM getrngfunc4(1);
455 SELECT * FROM vw_getrngfunc;
456 rngfuncid | rngfuncsubid | rngfuncname
457 -----------+--------------+-------------
458 1 | 1 | Joe
459 (1 row)
461 DROP VIEW vw_getrngfunc;
462 CREATE VIEW vw_getrngfunc AS SELECT * FROM getrngfunc4(1) WITH ORDINALITY AS t1(a,b,c,o);
463 SELECT * FROM vw_getrngfunc;
464 a | b | c | o
465 ---+---+-----+---
466 1 | 1 | Joe | 1
467 (1 row)
469 DROP VIEW vw_getrngfunc;
470 -- sql, proretset = t, prorettype = c
471 CREATE FUNCTION getrngfunc5(int) RETURNS setof rngfunc AS 'SELECT * FROM rngfunc WHERE rngfuncid = $1;' LANGUAGE SQL;
472 SELECT * FROM getrngfunc5(1) AS t1;
473 rngfuncid | rngfuncsubid | rngfuncname
474 -----------+--------------+-------------
475 1 | 1 | Joe
476 1 | 2 | Ed
477 (2 rows)
479 SELECT * FROM getrngfunc5(1) WITH ORDINALITY AS t1(a,b,c,o);
480 a | b | c | o
481 ---+---+-----+---
482 1 | 1 | Joe | 1
483 1 | 2 | Ed | 2
484 (2 rows)
486 CREATE VIEW vw_getrngfunc AS SELECT * FROM getrngfunc5(1);
487 SELECT * FROM vw_getrngfunc;
488 rngfuncid | rngfuncsubid | rngfuncname
489 -----------+--------------+-------------
490 1 | 1 | Joe
491 1 | 2 | Ed
492 (2 rows)
494 DROP VIEW vw_getrngfunc;
495 CREATE VIEW vw_getrngfunc AS SELECT * FROM getrngfunc5(1) WITH ORDINALITY AS t1(a,b,c,o);
496 SELECT * FROM vw_getrngfunc;
497 a | b | c | o
498 ---+---+-----+---
499 1 | 1 | Joe | 1
500 1 | 2 | Ed | 2
501 (2 rows)
503 DROP VIEW vw_getrngfunc;
504 -- sql, proretset = f, prorettype = record
505 CREATE FUNCTION getrngfunc6(int) RETURNS RECORD AS 'SELECT * FROM rngfunc WHERE rngfuncid = $1;' LANGUAGE SQL;
506 SELECT * FROM getrngfunc6(1) AS t1(rngfuncid int, rngfuncsubid int, rngfuncname text);
507 rngfuncid | rngfuncsubid | rngfuncname
508 -----------+--------------+-------------
509 1 | 1 | Joe
510 (1 row)
512 SELECT * FROM ROWS FROM( getrngfunc6(1) AS (rngfuncid int, rngfuncsubid int, rngfuncname text) ) WITH ORDINALITY;
513 rngfuncid | rngfuncsubid | rngfuncname | ordinality
514 -----------+--------------+-------------+------------
515 1 | 1 | Joe | 1
516 (1 row)
518 CREATE VIEW vw_getrngfunc AS SELECT * FROM getrngfunc6(1) AS
519 (rngfuncid int, rngfuncsubid int, rngfuncname text);
520 SELECT * FROM vw_getrngfunc;
521 rngfuncid | rngfuncsubid | rngfuncname
522 -----------+--------------+-------------
523 1 | 1 | Joe
524 (1 row)
526 DROP VIEW vw_getrngfunc;
527 CREATE VIEW vw_getrngfunc AS
528 SELECT * FROM ROWS FROM( getrngfunc6(1) AS (rngfuncid int, rngfuncsubid int, rngfuncname text) )
529 WITH ORDINALITY;
530 SELECT * FROM vw_getrngfunc;
531 rngfuncid | rngfuncsubid | rngfuncname | ordinality
532 -----------+--------------+-------------+------------
533 1 | 1 | Joe | 1
534 (1 row)
536 DROP VIEW vw_getrngfunc;
537 -- sql, proretset = t, prorettype = record
538 CREATE FUNCTION getrngfunc7(int) RETURNS setof record AS 'SELECT * FROM rngfunc WHERE rngfuncid = $1;' LANGUAGE SQL;
539 SELECT * FROM getrngfunc7(1) AS t1(rngfuncid int, rngfuncsubid int, rngfuncname text);
540 rngfuncid | rngfuncsubid | rngfuncname
541 -----------+--------------+-------------
542 1 | 1 | Joe
543 1 | 2 | Ed
544 (2 rows)
546 SELECT * FROM ROWS FROM( getrngfunc7(1) AS (rngfuncid int, rngfuncsubid int, rngfuncname text) ) WITH ORDINALITY;
547 rngfuncid | rngfuncsubid | rngfuncname | ordinality
548 -----------+--------------+-------------+------------
549 1 | 1 | Joe | 1
550 1 | 2 | Ed | 2
551 (2 rows)
553 CREATE VIEW vw_getrngfunc AS SELECT * FROM getrngfunc7(1) AS
554 (rngfuncid int, rngfuncsubid int, rngfuncname text);
555 SELECT * FROM vw_getrngfunc;
556 rngfuncid | rngfuncsubid | rngfuncname
557 -----------+--------------+-------------
558 1 | 1 | Joe
559 1 | 2 | Ed
560 (2 rows)
562 DROP VIEW vw_getrngfunc;
563 CREATE VIEW vw_getrngfunc AS
564 SELECT * FROM ROWS FROM( getrngfunc7(1) AS (rngfuncid int, rngfuncsubid int, rngfuncname text) )
565 WITH ORDINALITY;
566 SELECT * FROM vw_getrngfunc;
567 rngfuncid | rngfuncsubid | rngfuncname | ordinality
568 -----------+--------------+-------------+------------
569 1 | 1 | Joe | 1
570 1 | 2 | Ed | 2
571 (2 rows)
573 DROP VIEW vw_getrngfunc;
574 -- plpgsql, proretset = f, prorettype = b
575 CREATE FUNCTION getrngfunc8(int) RETURNS int AS 'DECLARE rngfuncint int; BEGIN SELECT rngfuncid into rngfuncint FROM rngfunc WHERE rngfuncid = $1; RETURN rngfuncint; END;' LANGUAGE plpgsql;
576 SELECT * FROM getrngfunc8(1) AS t1;
577 t1
578 ----
579 1
580 (1 row)
582 SELECT * FROM getrngfunc8(1) WITH ORDINALITY AS t1(v,o);
583 v | o
584 ---+---
585 1 | 1
586 (1 row)
588 CREATE VIEW vw_getrngfunc AS SELECT * FROM getrngfunc8(1);
589 SELECT * FROM vw_getrngfunc;
590 getrngfunc8
591 -------------
592 1
593 (1 row)
595 DROP VIEW vw_getrngfunc;
596 CREATE VIEW vw_getrngfunc AS SELECT * FROM getrngfunc8(1) WITH ORDINALITY AS t1(v,o);
597 SELECT * FROM vw_getrngfunc;
598 v | o
599 ---+---
600 1 | 1
601 (1 row)
603 DROP VIEW vw_getrngfunc;
604 -- plpgsql, proretset = f, prorettype = c
605 CREATE FUNCTION getrngfunc9(int) RETURNS rngfunc AS 'DECLARE rngfunctup rngfunc%ROWTYPE; BEGIN SELECT * into rngfunctup FROM rngfunc WHERE rngfuncid = $1; RETURN rngfunctup; END;' LANGUAGE plpgsql;
606 SELECT * FROM getrngfunc9(1) AS t1;
607 rngfuncid | rngfuncsubid | rngfuncname
608 -----------+--------------+-------------
609 1 | 1 | Joe
610 (1 row)
612 SELECT * FROM getrngfunc9(1) WITH ORDINALITY AS t1(a,b,c,o);
613 a | b | c | o
614 ---+---+-----+---
615 1 | 1 | Joe | 1
616 (1 row)
618 CREATE VIEW vw_getrngfunc AS SELECT * FROM getrngfunc9(1);
619 SELECT * FROM vw_getrngfunc;
620 rngfuncid | rngfuncsubid | rngfuncname
621 -----------+--------------+-------------
622 1 | 1 | Joe
623 (1 row)
625 DROP VIEW vw_getrngfunc;
626 CREATE VIEW vw_getrngfunc AS SELECT * FROM getrngfunc9(1) WITH ORDINALITY AS t1(a,b,c,o);
627 SELECT * FROM vw_getrngfunc;
628 a | b | c | o
629 ---+---+-----+---
630 1 | 1 | Joe | 1
631 (1 row)
633 DROP VIEW vw_getrngfunc;
634 -- mix 'n match kinds, to exercise expandRTE and related logic
635 select * from rows from(getrngfunc1(1),getrngfunc2(1),getrngfunc3(1),getrngfunc4(1),getrngfunc5(1),
636 getrngfunc6(1) AS (rngfuncid int, rngfuncsubid int, rngfuncname text),
637 getrngfunc7(1) AS (rngfuncid int, rngfuncsubid int, rngfuncname text),
638 getrngfunc8(1),getrngfunc9(1))
639 with ordinality as t1(a,b,c,d,e,f,g,h,i,j,k,l,m,o,p,q,r,s,t,u);
640 a | b | c | d | e | f | g | h | i | j | k | l | m | o | p | q | r | s | t | u
641 ---+---+-----+---+---+-----+---+---+-----+---+---+-----+---+---+-----+---+---+---+-----+---
642 1 | 1 | Joe | 1 | 1 | Joe | 1 | 1 | Joe | 1 | 1 | Joe | 1 | 1 | Joe | 1 | 1 | 1 | Joe | 1
643 | 1 | Ed | | | | 1 | 2 | Ed | | | | 1 | 2 | Ed | | | | | 2
644 (2 rows)
646 select * from rows from(getrngfunc9(1),getrngfunc8(1),
647 getrngfunc7(1) AS (rngfuncid int, rngfuncsubid int, rngfuncname text),
648 getrngfunc6(1) AS (rngfuncid int, rngfuncsubid int, rngfuncname text),
649 getrngfunc5(1),getrngfunc4(1),getrngfunc3(1),getrngfunc2(1),getrngfunc1(1))
650 with ordinality as t1(a,b,c,d,e,f,g,h,i,j,k,l,m,o,p,q,r,s,t,u);
651 a | b | c | d | e | f | g | h | i | j | k | l | m | o | p | q | r | s | t | u
652 ---+---+-----+---+---+---+-----+---+---+-----+---+---+-----+---+---+-----+-----+---+---+---
653 1 | 1 | Joe | 1 | 1 | 1 | Joe | 1 | 1 | Joe | 1 | 1 | Joe | 1 | 1 | Joe | Joe | 1 | 1 | 1
654 | | | | 1 | 2 | Ed | | | | 1 | 2 | Ed | | | | Ed | 1 | | 2
655 (2 rows)
657 create temporary view vw_rngfunc as
658 select * from rows from(getrngfunc9(1),
659 getrngfunc7(1) AS (rngfuncid int, rngfuncsubid int, rngfuncname text),
660 getrngfunc1(1))
661 with ordinality as t1(a,b,c,d,e,f,g,n);
662 select * from vw_rngfunc;
663 a | b | c | d | e | f | g | n
664 ---+---+-----+---+---+-----+---+---
665 1 | 1 | Joe | 1 | 1 | Joe | 1 | 1
666 | | | 1 | 2 | Ed | | 2
667 (2 rows)
669 select pg_get_viewdef('vw_rngfunc');
670 pg_get_viewdef
671 ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
672 SELECT a, +
673 b, +
674 c, +
675 d, +
676 e, +
677 f, +
678 g, +
679 n +
680 FROM ROWS FROM(getrngfunc9(1), getrngfunc7(1) AS (rngfuncid integer, rngfuncsubid integer, rngfuncname text), getrngfunc1(1)) WITH ORDINALITY t1(a, b, c, d, e, f, g, n);
681 (1 row)
683 drop view vw_rngfunc;
684 DROP FUNCTION getrngfunc1(int);
685 DROP FUNCTION getrngfunc2(int);
686 DROP FUNCTION getrngfunc3(int);
687 DROP FUNCTION getrngfunc4(int);
688 DROP FUNCTION getrngfunc5(int);
689 DROP FUNCTION getrngfunc6(int);
690 DROP FUNCTION getrngfunc7(int);
691 DROP FUNCTION getrngfunc8(int);
692 DROP FUNCTION getrngfunc9(int);
693 DROP FUNCTION rngfunct(int);
694 DROP TABLE rngfunc2;
695 DROP TABLE rngfunc;
696 -- Rescan tests --
697 CREATE TEMPORARY SEQUENCE rngfunc_rescan_seq1;
698 CREATE TEMPORARY SEQUENCE rngfunc_rescan_seq2;
699 CREATE TYPE rngfunc_rescan_t AS (i integer, s bigint);
700 CREATE FUNCTION rngfunc_sql(int,int) RETURNS setof rngfunc_rescan_t AS 'SELECT i, nextval(''rngfunc_rescan_seq1'') FROM generate_series($1,$2) i;' LANGUAGE SQL;
701 -- plpgsql functions use materialize mode
702 CREATE FUNCTION rngfunc_mat(int,int) RETURNS setof rngfunc_rescan_t AS 'begin for i in $1..$2 loop return next (i, nextval(''rngfunc_rescan_seq2'')); end loop; end;' LANGUAGE plpgsql;
703 --invokes ExecReScanFunctionScan - all these cases should materialize the function only once
704 -- LEFT JOIN on a condition that the planner can't prove to be true is used to ensure the function
705 -- is on the inner path of a nestloop join
706 SELECT setval('rngfunc_rescan_seq1',1,false),setval('rngfunc_rescan_seq2',1,false);
707 setval | setval
708 --------+--------
709 1 | 1
710 (1 row)
712 SELECT * FROM (VALUES (1),(2),(3)) v(r) LEFT JOIN rngfunc_sql(11,13) ON (r+i)<100;
713 r | i | s
714 ---+----+---
715 1 | 11 | 1
716 1 | 12 | 2
717 1 | 13 | 3
718 2 | 11 | 1
719 2 | 12 | 2
720 2 | 13 | 3
721 3 | 11 | 1
722 3 | 12 | 2
723 3 | 13 | 3
724 (9 rows)
726 SELECT setval('rngfunc_rescan_seq1',1,false),setval('rngfunc_rescan_seq2',1,false);
727 setval | setval
728 --------+--------
729 1 | 1
730 (1 row)
732 SELECT * FROM (VALUES (1),(2),(3)) v(r) LEFT JOIN rngfunc_sql(11,13) WITH ORDINALITY AS f(i,s,o) ON (r+i)<100;
733 r | i | s | o
734 ---+----+---+---
735 1 | 11 | 1 | 1
736 1 | 12 | 2 | 2
737 1 | 13 | 3 | 3
738 2 | 11 | 1 | 1
739 2 | 12 | 2 | 2
740 2 | 13 | 3 | 3
741 3 | 11 | 1 | 1
742 3 | 12 | 2 | 2
743 3 | 13 | 3 | 3
744 (9 rows)
746 SELECT setval('rngfunc_rescan_seq1',1,false),setval('rngfunc_rescan_seq2',1,false);
747 setval | setval
748 --------+--------
749 1 | 1
750 (1 row)
752 SELECT * FROM (VALUES (1),(2),(3)) v(r) LEFT JOIN rngfunc_mat(11,13) ON (r+i)<100;
753 r | i | s
754 ---+----+---
755 1 | 11 | 1
756 1 | 12 | 2
757 1 | 13 | 3
758 2 | 11 | 1
759 2 | 12 | 2
760 2 | 13 | 3
761 3 | 11 | 1
762 3 | 12 | 2
763 3 | 13 | 3
764 (9 rows)
766 SELECT setval('rngfunc_rescan_seq1',1,false),setval('rngfunc_rescan_seq2',1,false);
767 setval | setval
768 --------+--------
769 1 | 1
770 (1 row)
772 SELECT * FROM (VALUES (1),(2),(3)) v(r) LEFT JOIN rngfunc_mat(11,13) WITH ORDINALITY AS f(i,s,o) ON (r+i)<100;
773 r | i | s | o
774 ---+----+---+---
775 1 | 11 | 1 | 1
776 1 | 12 | 2 | 2
777 1 | 13 | 3 | 3
778 2 | 11 | 1 | 1
779 2 | 12 | 2 | 2
780 2 | 13 | 3 | 3
781 3 | 11 | 1 | 1
782 3 | 12 | 2 | 2
783 3 | 13 | 3 | 3
784 (9 rows)
786 SELECT setval('rngfunc_rescan_seq1',1,false),setval('rngfunc_rescan_seq2',1,false);
787 setval | setval
788 --------+--------
789 1 | 1
790 (1 row)
792 SELECT * FROM (VALUES (1),(2),(3)) v(r) LEFT JOIN ROWS FROM( rngfunc_sql(11,13), rngfunc_mat(11,13) ) WITH ORDINALITY AS f(i1,s1,i2,s2,o) ON (r+i1+i2)<100;
793 r | i1 | s1 | i2 | s2 | o
794 ---+----+----+----+----+---
795 1 | 11 | 1 | 11 | 1 | 1
796 1 | 12 | 2 | 12 | 2 | 2
797 1 | 13 | 3 | 13 | 3 | 3
798 2 | 11 | 1 | 11 | 1 | 1
799 2 | 12 | 2 | 12 | 2 | 2
800 2 | 13 | 3 | 13 | 3 | 3
801 3 | 11 | 1 | 11 | 1 | 1
802 3 | 12 | 2 | 12 | 2 | 2
803 3 | 13 | 3 | 13 | 3 | 3
804 (9 rows)
806 SELECT * FROM (VALUES (1),(2),(3)) v(r) LEFT JOIN generate_series(11,13) f(i) ON (r+i)<100;
807 r | i
808 ---+----
809 1 | 11
810 1 | 12
811 1 | 13
812 2 | 11
813 2 | 12
814 2 | 13
815 3 | 11
816 3 | 12
817 3 | 13
818 (9 rows)
820 SELECT * FROM (VALUES (1),(2),(3)) v(r) LEFT JOIN generate_series(11,13) WITH ORDINALITY AS f(i,o) ON (r+i)<100;
821 r | i | o
822 ---+----+---
823 1 | 11 | 1
824 1 | 12 | 2
825 1 | 13 | 3
826 2 | 11 | 1
827 2 | 12 | 2
828 2 | 13 | 3
829 3 | 11 | 1
830 3 | 12 | 2
831 3 | 13 | 3
832 (9 rows)
834 SELECT * FROM (VALUES (1),(2),(3)) v(r) LEFT JOIN unnest(array[10,20,30]) f(i) ON (r+i)<100;
835 r | i
836 ---+----
837 1 | 10
838 1 | 20
839 1 | 30
840 2 | 10
841 2 | 20
842 2 | 30
843 3 | 10
844 3 | 20
845 3 | 30
846 (9 rows)
848 SELECT * FROM (VALUES (1),(2),(3)) v(r) LEFT JOIN unnest(array[10,20,30]) WITH ORDINALITY AS f(i,o) ON (r+i)<100;
849 r | i | o
850 ---+----+---
851 1 | 10 | 1
852 1 | 20 | 2
853 1 | 30 | 3
854 2 | 10 | 1
855 2 | 20 | 2
856 2 | 30 | 3
857 3 | 10 | 1
858 3 | 20 | 2
859 3 | 30 | 3
860 (9 rows)
862 --invokes ExecReScanFunctionScan with chgParam != NULL (using implied LATERAL)
863 SELECT setval('rngfunc_rescan_seq1',1,false),setval('rngfunc_rescan_seq2',1,false);
864 setval | setval
865 --------+--------
866 1 | 1
867 (1 row)
869 SELECT * FROM (VALUES (1),(2),(3)) v(r), rngfunc_sql(10+r,13);
870 r | i | s
871 ---+----+---
872 1 | 11 | 1
873 1 | 12 | 2
874 1 | 13 | 3
875 2 | 12 | 4
876 2 | 13 | 5
877 3 | 13 | 6
878 (6 rows)
880 SELECT setval('rngfunc_rescan_seq1',1,false),setval('rngfunc_rescan_seq2',1,false);
881 setval | setval
882 --------+--------
883 1 | 1
884 (1 row)
886 SELECT * FROM (VALUES (1),(2),(3)) v(r), rngfunc_sql(10+r,13) WITH ORDINALITY AS f(i,s,o);
887 r | i | s | o
888 ---+----+---+---
889 1 | 11 | 1 | 1
890 1 | 12 | 2 | 2
891 1 | 13 | 3 | 3
892 2 | 12 | 4 | 1
893 2 | 13 | 5 | 2
894 3 | 13 | 6 | 1
895 (6 rows)
897 SELECT setval('rngfunc_rescan_seq1',1,false),setval('rngfunc_rescan_seq2',1,false);
898 setval | setval
899 --------+--------
900 1 | 1
901 (1 row)
903 SELECT * FROM (VALUES (1),(2),(3)) v(r), rngfunc_sql(11,10+r);
904 r | i | s
905 ---+----+---
906 1 | 11 | 1
907 2 | 11 | 2
908 2 | 12 | 3
909 3 | 11 | 4
910 3 | 12 | 5
911 3 | 13 | 6
912 (6 rows)
914 SELECT setval('rngfunc_rescan_seq1',1,false),setval('rngfunc_rescan_seq2',1,false);
915 setval | setval
916 --------+--------
917 1 | 1
918 (1 row)
920 SELECT * FROM (VALUES (1),(2),(3)) v(r), rngfunc_sql(11,10+r) WITH ORDINALITY AS f(i,s,o);
921 r | i | s | o
922 ---+----+---+---
923 1 | 11 | 1 | 1
924 2 | 11 | 2 | 1
925 2 | 12 | 3 | 2
926 3 | 11 | 4 | 1
927 3 | 12 | 5 | 2
928 3 | 13 | 6 | 3
929 (6 rows)
931 SELECT setval('rngfunc_rescan_seq1',1,false),setval('rngfunc_rescan_seq2',1,false);
932 setval | setval
933 --------+--------
934 1 | 1
935 (1 row)
937 SELECT * FROM (VALUES (11,12),(13,15),(16,20)) v(r1,r2), rngfunc_sql(r1,r2);
938 r1 | r2 | i | s
939 ----+----+----+----
940 11 | 12 | 11 | 1
941 11 | 12 | 12 | 2
942 13 | 15 | 13 | 3
943 13 | 15 | 14 | 4
944 13 | 15 | 15 | 5
945 16 | 20 | 16 | 6
946 16 | 20 | 17 | 7
947 16 | 20 | 18 | 8
948 16 | 20 | 19 | 9
949 16 | 20 | 20 | 10
950 (10 rows)
952 SELECT setval('rngfunc_rescan_seq1',1,false),setval('rngfunc_rescan_seq2',1,false);
953 setval | setval
954 --------+--------
955 1 | 1
956 (1 row)
958 SELECT * FROM (VALUES (11,12),(13,15),(16,20)) v(r1,r2), rngfunc_sql(r1,r2) WITH ORDINALITY AS f(i,s,o);
959 r1 | r2 | i | s | o
960 ----+----+----+----+---
961 11 | 12 | 11 | 1 | 1
962 11 | 12 | 12 | 2 | 2
963 13 | 15 | 13 | 3 | 1
964 13 | 15 | 14 | 4 | 2
965 13 | 15 | 15 | 5 | 3
966 16 | 20 | 16 | 6 | 1
967 16 | 20 | 17 | 7 | 2
968 16 | 20 | 18 | 8 | 3
969 16 | 20 | 19 | 9 | 4
970 16 | 20 | 20 | 10 | 5
971 (10 rows)
973 SELECT setval('rngfunc_rescan_seq1',1,false),setval('rngfunc_rescan_seq2',1,false);
974 setval | setval
975 --------+--------
976 1 | 1
977 (1 row)
979 SELECT * FROM (VALUES (1),(2),(3)) v(r), rngfunc_mat(10+r,13);
980 r | i | s
981 ---+----+---
982 1 | 11 | 1
983 1 | 12 | 2
984 1 | 13 | 3
985 2 | 12 | 4
986 2 | 13 | 5
987 3 | 13 | 6
988 (6 rows)
990 SELECT setval('rngfunc_rescan_seq1',1,false),setval('rngfunc_rescan_seq2',1,false);
991 setval | setval
992 --------+--------
993 1 | 1
994 (1 row)
996 SELECT * FROM (VALUES (1),(2),(3)) v(r), rngfunc_mat(10+r,13) WITH ORDINALITY AS f(i,s,o);
997 r | i | s | o
998 ---+----+---+---
999 1 | 11 | 1 | 1
1000 1 | 12 | 2 | 2
1001 1 | 13 | 3 | 3
1002 2 | 12 | 4 | 1
1003 2 | 13 | 5 | 2
1004 3 | 13 | 6 | 1
1005 (6 rows)
1007 SELECT setval('rngfunc_rescan_seq1',1,false),setval('rngfunc_rescan_seq2',1,false);
1008 setval | setval
1009 --------+--------
1010 1 | 1
1011 (1 row)
1013 SELECT * FROM (VALUES (1),(2),(3)) v(r), rngfunc_mat(11,10+r);
1014 r | i | s
1015 ---+----+---
1016 1 | 11 | 1
1017 2 | 11 | 2
1018 2 | 12 | 3
1019 3 | 11 | 4
1020 3 | 12 | 5
1021 3 | 13 | 6
1022 (6 rows)
1024 SELECT setval('rngfunc_rescan_seq1',1,false),setval('rngfunc_rescan_seq2',1,false);
1025 setval | setval
1026 --------+--------
1027 1 | 1
1028 (1 row)
1030 SELECT * FROM (VALUES (1),(2),(3)) v(r), rngfunc_mat(11,10+r) WITH ORDINALITY AS f(i,s,o);
1031 r | i | s | o
1032 ---+----+---+---
1033 1 | 11 | 1 | 1
1034 2 | 11 | 2 | 1
1035 2 | 12 | 3 | 2
1036 3 | 11 | 4 | 1
1037 3 | 12 | 5 | 2
1038 3 | 13 | 6 | 3
1039 (6 rows)
1041 SELECT setval('rngfunc_rescan_seq1',1,false),setval('rngfunc_rescan_seq2',1,false);
1042 setval | setval
1043 --------+--------
1044 1 | 1
1045 (1 row)
1047 SELECT * FROM (VALUES (11,12),(13,15),(16,20)) v(r1,r2), rngfunc_mat(r1,r2);
1048 r1 | r2 | i | s
1049 ----+----+----+----
1050 11 | 12 | 11 | 1
1051 11 | 12 | 12 | 2
1052 13 | 15 | 13 | 3
1053 13 | 15 | 14 | 4
1054 13 | 15 | 15 | 5
1055 16 | 20 | 16 | 6
1056 16 | 20 | 17 | 7
1057 16 | 20 | 18 | 8
1058 16 | 20 | 19 | 9
1059 16 | 20 | 20 | 10
1060 (10 rows)
1062 SELECT setval('rngfunc_rescan_seq1',1,false),setval('rngfunc_rescan_seq2',1,false);
1063 setval | setval
1064 --------+--------
1065 1 | 1
1066 (1 row)
1068 SELECT * FROM (VALUES (11,12),(13,15),(16,20)) v(r1,r2), rngfunc_mat(r1,r2) WITH ORDINALITY AS f(i,s,o);
1069 r1 | r2 | i | s | o
1070 ----+----+----+----+---
1071 11 | 12 | 11 | 1 | 1
1072 11 | 12 | 12 | 2 | 2
1073 13 | 15 | 13 | 3 | 1
1074 13 | 15 | 14 | 4 | 2
1075 13 | 15 | 15 | 5 | 3
1076 16 | 20 | 16 | 6 | 1
1077 16 | 20 | 17 | 7 | 2
1078 16 | 20 | 18 | 8 | 3
1079 16 | 20 | 19 | 9 | 4
1080 16 | 20 | 20 | 10 | 5
1081 (10 rows)
1083 -- selective rescan of multiple functions:
1084 SELECT setval('rngfunc_rescan_seq1',1,false),setval('rngfunc_rescan_seq2',1,false);
1085 setval | setval
1086 --------+--------
1087 1 | 1
1088 (1 row)
1090 SELECT * FROM (VALUES (1),(2),(3)) v(r), ROWS FROM( rngfunc_sql(11,11), rngfunc_mat(10+r,13) );
1091 r | i | s | i | s
1092 ---+----+---+----+---
1093 1 | 11 | 1 | 11 | 1
1094 1 | | | 12 | 2
1095 1 | | | 13 | 3
1096 2 | 11 | 1 | 12 | 4
1097 2 | | | 13 | 5
1098 3 | 11 | 1 | 13 | 6
1099 (6 rows)
1101 SELECT setval('rngfunc_rescan_seq1',1,false),setval('rngfunc_rescan_seq2',1,false);
1102 setval | setval
1103 --------+--------
1104 1 | 1
1105 (1 row)
1107 SELECT * FROM (VALUES (1),(2),(3)) v(r), ROWS FROM( rngfunc_sql(10+r,13), rngfunc_mat(11,11) );
1108 r | i | s | i | s
1109 ---+----+---+----+---
1110 1 | 11 | 1 | 11 | 1
1111 1 | 12 | 2 | |
1112 1 | 13 | 3 | |
1113 2 | 12 | 4 | 11 | 1
1114 2 | 13 | 5 | |
1115 3 | 13 | 6 | 11 | 1
1116 (6 rows)
1118 SELECT setval('rngfunc_rescan_seq1',1,false),setval('rngfunc_rescan_seq2',1,false);
1119 setval | setval
1120 --------+--------
1121 1 | 1
1122 (1 row)
1124 SELECT * FROM (VALUES (1),(2),(3)) v(r), ROWS FROM( rngfunc_sql(10+r,13), rngfunc_mat(10+r,13) );
1125 r | i | s | i | s
1126 ---+----+---+----+---
1127 1 | 11 | 1 | 11 | 1
1128 1 | 12 | 2 | 12 | 2
1129 1 | 13 | 3 | 13 | 3
1130 2 | 12 | 4 | 12 | 4
1131 2 | 13 | 5 | 13 | 5
1132 3 | 13 | 6 | 13 | 6
1133 (6 rows)
1135 SELECT setval('rngfunc_rescan_seq1',1,false),setval('rngfunc_rescan_seq2',1,false);
1136 setval | setval
1137 --------+--------
1138 1 | 1
1139 (1 row)
1141 SELECT * FROM generate_series(1,2) r1, generate_series(r1,3) r2, ROWS FROM( rngfunc_sql(10+r1,13), rngfunc_mat(10+r2,13) );
1142 r1 | r2 | i | s | i | s
1143 ----+----+----+----+----+---
1144 1 | 1 | 11 | 1 | 11 | 1
1145 1 | 1 | 12 | 2 | 12 | 2
1146 1 | 1 | 13 | 3 | 13 | 3
1147 1 | 2 | 11 | 4 | 12 | 4
1148 1 | 2 | 12 | 5 | 13 | 5
1149 1 | 2 | 13 | 6 | |
1150 1 | 3 | 11 | 7 | 13 | 6
1151 1 | 3 | 12 | 8 | |
1152 1 | 3 | 13 | 9 | |
1153 2 | 2 | 12 | 10 | 12 | 7
1154 2 | 2 | 13 | 11 | 13 | 8
1155 2 | 3 | 12 | 12 | 13 | 9
1156 2 | 3 | 13 | 13 | |
1157 (13 rows)
1159 SELECT * FROM (VALUES (1),(2),(3)) v(r), generate_series(10+r,20-r) f(i);
1160 r | i
1161 ---+----
1162 1 | 11
1163 1 | 12
1164 1 | 13
1165 1 | 14
1166 1 | 15
1167 1 | 16
1168 1 | 17
1169 1 | 18
1170 1 | 19
1171 2 | 12
1172 2 | 13
1173 2 | 14
1174 2 | 15
1175 2 | 16
1176 2 | 17
1177 2 | 18
1178 3 | 13
1179 3 | 14
1180 3 | 15
1181 3 | 16
1182 3 | 17
1183 (21 rows)
1185 SELECT * FROM (VALUES (1),(2),(3)) v(r), generate_series(10+r,20-r) WITH ORDINALITY AS f(i,o);
1186 r | i | o
1187 ---+----+---
1188 1 | 11 | 1
1189 1 | 12 | 2
1190 1 | 13 | 3
1191 1 | 14 | 4
1192 1 | 15 | 5
1193 1 | 16 | 6
1194 1 | 17 | 7
1195 1 | 18 | 8
1196 1 | 19 | 9
1197 2 | 12 | 1
1198 2 | 13 | 2
1199 2 | 14 | 3
1200 2 | 15 | 4
1201 2 | 16 | 5
1202 2 | 17 | 6
1203 2 | 18 | 7
1204 3 | 13 | 1
1205 3 | 14 | 2
1206 3 | 15 | 3
1207 3 | 16 | 4
1208 3 | 17 | 5
1209 (21 rows)
1211 SELECT * FROM (VALUES (1),(2),(3)) v(r), unnest(array[r*10,r*20,r*30]) f(i);
1212 r | i
1213 ---+----
1214 1 | 10
1215 1 | 20
1216 1 | 30
1217 2 | 20
1218 2 | 40
1219 2 | 60
1220 3 | 30
1221 3 | 60
1222 3 | 90
1223 (9 rows)
1225 SELECT * FROM (VALUES (1),(2),(3)) v(r), unnest(array[r*10,r*20,r*30]) WITH ORDINALITY AS f(i,o);
1226 r | i | o
1227 ---+----+---
1228 1 | 10 | 1
1229 1 | 20 | 2
1230 1 | 30 | 3
1231 2 | 20 | 1
1232 2 | 40 | 2
1233 2 | 60 | 3
1234 3 | 30 | 1
1235 3 | 60 | 2
1236 3 | 90 | 3
1237 (9 rows)
1239 -- deep nesting
1240 SELECT * FROM (VALUES (1),(2),(3)) v1(r1),
1241 LATERAL (SELECT r1, * FROM (VALUES (10),(20),(30)) v2(r2)
1242 LEFT JOIN generate_series(21,23) f(i) ON ((r2+i)<100) OFFSET 0) s1;
1243 r1 | r1 | r2 | i
1244 ----+----+----+----
1245 1 | 1 | 10 | 21
1246 1 | 1 | 10 | 22
1247 1 | 1 | 10 | 23
1248 1 | 1 | 20 | 21
1249 1 | 1 | 20 | 22
1250 1 | 1 | 20 | 23
1251 1 | 1 | 30 | 21
1252 1 | 1 | 30 | 22
1253 1 | 1 | 30 | 23
1254 2 | 2 | 10 | 21
1255 2 | 2 | 10 | 22
1256 2 | 2 | 10 | 23
1257 2 | 2 | 20 | 21
1258 2 | 2 | 20 | 22
1259 2 | 2 | 20 | 23
1260 2 | 2 | 30 | 21
1261 2 | 2 | 30 | 22
1262 2 | 2 | 30 | 23
1263 3 | 3 | 10 | 21
1264 3 | 3 | 10 | 22
1265 3 | 3 | 10 | 23
1266 3 | 3 | 20 | 21
1267 3 | 3 | 20 | 22
1268 3 | 3 | 20 | 23
1269 3 | 3 | 30 | 21
1270 3 | 3 | 30 | 22
1271 3 | 3 | 30 | 23
1272 (27 rows)
1274 SELECT * FROM (VALUES (1),(2),(3)) v1(r1),
1275 LATERAL (SELECT r1, * FROM (VALUES (10),(20),(30)) v2(r2)
1276 LEFT JOIN generate_series(20+r1,23) f(i) ON ((r2+i)<100) OFFSET 0) s1;
1277 r1 | r1 | r2 | i
1278 ----+----+----+----
1279 1 | 1 | 10 | 21
1280 1 | 1 | 10 | 22
1281 1 | 1 | 10 | 23
1282 1 | 1 | 20 | 21
1283 1 | 1 | 20 | 22
1284 1 | 1 | 20 | 23
1285 1 | 1 | 30 | 21
1286 1 | 1 | 30 | 22
1287 1 | 1 | 30 | 23
1288 2 | 2 | 10 | 22
1289 2 | 2 | 10 | 23
1290 2 | 2 | 20 | 22
1291 2 | 2 | 20 | 23
1292 2 | 2 | 30 | 22
1293 2 | 2 | 30 | 23
1294 3 | 3 | 10 | 23
1295 3 | 3 | 20 | 23
1296 3 | 3 | 30 | 23
1297 (18 rows)
1299 SELECT * FROM (VALUES (1),(2),(3)) v1(r1),
1300 LATERAL (SELECT r1, * FROM (VALUES (10),(20),(30)) v2(r2)
1301 LEFT JOIN generate_series(r2,r2+3) f(i) ON ((r2+i)<100) OFFSET 0) s1;
1302 r1 | r1 | r2 | i
1303 ----+----+----+----
1304 1 | 1 | 10 | 10
1305 1 | 1 | 10 | 11
1306 1 | 1 | 10 | 12
1307 1 | 1 | 10 | 13
1308 1 | 1 | 20 | 20
1309 1 | 1 | 20 | 21
1310 1 | 1 | 20 | 22
1311 1 | 1 | 20 | 23
1312 1 | 1 | 30 | 30
1313 1 | 1 | 30 | 31
1314 1 | 1 | 30 | 32
1315 1 | 1 | 30 | 33
1316 2 | 2 | 10 | 10
1317 2 | 2 | 10 | 11
1318 2 | 2 | 10 | 12
1319 2 | 2 | 10 | 13
1320 2 | 2 | 20 | 20
1321 2 | 2 | 20 | 21
1322 2 | 2 | 20 | 22
1323 2 | 2 | 20 | 23
1324 2 | 2 | 30 | 30
1325 2 | 2 | 30 | 31
1326 2 | 2 | 30 | 32
1327 2 | 2 | 30 | 33
1328 3 | 3 | 10 | 10
1329 3 | 3 | 10 | 11
1330 3 | 3 | 10 | 12
1331 3 | 3 | 10 | 13
1332 3 | 3 | 20 | 20
1333 3 | 3 | 20 | 21
1334 3 | 3 | 20 | 22
1335 3 | 3 | 20 | 23
1336 3 | 3 | 30 | 30
1337 3 | 3 | 30 | 31
1338 3 | 3 | 30 | 32
1339 3 | 3 | 30 | 33
1340 (36 rows)
1342 SELECT * FROM (VALUES (1),(2),(3)) v1(r1),
1343 LATERAL (SELECT r1, * FROM (VALUES (10),(20),(30)) v2(r2)
1344 LEFT JOIN generate_series(r1,2+r2/5) f(i) ON ((r2+i)<100) OFFSET 0) s1;
1345 r1 | r1 | r2 | i
1346 ----+----+----+---
1347 1 | 1 | 10 | 1
1348 1 | 1 | 10 | 2
1349 1 | 1 | 10 | 3
1350 1 | 1 | 10 | 4
1351 1 | 1 | 20 | 1
1352 1 | 1 | 20 | 2
1353 1 | 1 | 20 | 3
1354 1 | 1 | 20 | 4
1355 1 | 1 | 20 | 5
1356 1 | 1 | 20 | 6
1357 1 | 1 | 30 | 1
1358 1 | 1 | 30 | 2
1359 1 | 1 | 30 | 3
1360 1 | 1 | 30 | 4
1361 1 | 1 | 30 | 5
1362 1 | 1 | 30 | 6
1363 1 | 1 | 30 | 7
1364 1 | 1 | 30 | 8
1365 2 | 2 | 10 | 2
1366 2 | 2 | 10 | 3
1367 2 | 2 | 10 | 4
1368 2 | 2 | 20 | 2
1369 2 | 2 | 20 | 3
1370 2 | 2 | 20 | 4
1371 2 | 2 | 20 | 5
1372 2 | 2 | 20 | 6
1373 2 | 2 | 30 | 2
1374 2 | 2 | 30 | 3
1375 2 | 2 | 30 | 4
1376 2 | 2 | 30 | 5
1377 2 | 2 | 30 | 6
1378 2 | 2 | 30 | 7
1379 2 | 2 | 30 | 8
1380 3 | 3 | 10 | 3
1381 3 | 3 | 10 | 4
1382 3 | 3 | 20 | 3
1383 3 | 3 | 20 | 4
1384 3 | 3 | 20 | 5
1385 3 | 3 | 20 | 6
1386 3 | 3 | 30 | 3
1387 3 | 3 | 30 | 4
1388 3 | 3 | 30 | 5
1389 3 | 3 | 30 | 6
1390 3 | 3 | 30 | 7
1391 3 | 3 | 30 | 8
1392 (45 rows)
1394 -- check handling of FULL JOIN with multiple lateral references (bug #15741)
1395 SELECT *
1396 FROM (VALUES (1),(2)) v1(r1)
1397 LEFT JOIN LATERAL (
1398 SELECT *
1399 FROM generate_series(1, v1.r1) AS gs1
1400 LEFT JOIN LATERAL (
1401 SELECT *
1402 FROM generate_series(1, gs1) AS gs2
1403 LEFT JOIN generate_series(1, gs2) AS gs3 ON TRUE
1404 ) AS ss1 ON TRUE
1405 FULL JOIN generate_series(1, v1.r1) AS gs4 ON FALSE
1406 ) AS ss0 ON TRUE;
1407 r1 | gs1 | gs2 | gs3 | gs4
1408 ----+-----+-----+-----+-----
1409 1 | | | | 1
1410 1 | 1 | 1 | 1 |
1411 2 | | | | 1
1412 2 | | | | 2
1413 2 | 1 | 1 | 1 |
1414 2 | 2 | 1 | 1 |
1415 2 | 2 | 2 | 1 |
1416 2 | 2 | 2 | 2 |
1417 (8 rows)
1419 DROP FUNCTION rngfunc_sql(int,int);
1420 DROP FUNCTION rngfunc_mat(int,int);
1421 DROP SEQUENCE rngfunc_rescan_seq1;
1422 DROP SEQUENCE rngfunc_rescan_seq2;
1423 --
1424 -- Test cases involving OUT parameters
1425 --
1426 CREATE FUNCTION rngfunc(in f1 int, out f2 int)
1427 AS 'select $1+1' LANGUAGE sql;
1428 SELECT rngfunc(42);
1429 rngfunc
1430 ---------
1431 43
1432 (1 row)
1434 SELECT * FROM rngfunc(42);
1435 f2
1436 ----
1437 43
1438 (1 row)
1440 SELECT * FROM rngfunc(42) AS p(x);
1441 x
1442 ----
1443 43
1444 (1 row)
1446 -- explicit spec of return type is OK
1447 CREATE OR REPLACE FUNCTION rngfunc(in f1 int, out f2 int) RETURNS int
1448 AS 'select $1+1' LANGUAGE sql;
1449 -- error, wrong result type
1450 CREATE OR REPLACE FUNCTION rngfunc(in f1 int, out f2 int) RETURNS float
1451 AS 'select $1+1' LANGUAGE sql;
1452 ERROR: function result type must be integer because of OUT parameters
1453 -- with multiple OUT params you must get a RECORD result
1454 CREATE OR REPLACE FUNCTION rngfunc(in f1 int, out f2 int, out f3 text) RETURNS int
1455 AS 'select $1+1' LANGUAGE sql;
1456 ERROR: function result type must be record because of OUT parameters
1457 CREATE OR REPLACE FUNCTION rngfunc(in f1 int, out f2 int, out f3 text)
1458 RETURNS record
1459 AS 'select $1+1' LANGUAGE sql;
1460 ERROR: cannot change return type of existing function
1461 HINT: Use DROP FUNCTION rngfunc(integer) first.
1462 CREATE OR REPLACE FUNCTION rngfuncr(in f1 int, out f2 int, out text)
1463 AS $$select $1-1, $1::text || 'z'$$ LANGUAGE sql;
1464 SELECT f1, rngfuncr(f1) FROM int4_tbl;
1465 f1 | rngfuncr
1466 -------------+----------------------------
1467 0 | (-1,0z)
1468 123456 | (123455,123456z)
1469 -123456 | (-123457,-123456z)
1470 2147483647 | (2147483646,2147483647z)
1471 -2147483647 | (-2147483648,-2147483647z)
1472 (5 rows)
1474 SELECT * FROM rngfuncr(42);
1475 f2 | column2
1476 ----+---------
1477 41 | 42z
1478 (1 row)
1480 SELECT * FROM rngfuncr(42) AS p(a,b);
1481 a | b
1482 ----+-----
1483 41 | 42z
1484 (1 row)
1486 CREATE OR REPLACE FUNCTION rngfuncb(in f1 int, inout f2 int, out text)
1487 AS $$select $2-1, $1::text || 'z'$$ LANGUAGE sql;
1488 SELECT f1, rngfuncb(f1, f1/2) FROM int4_tbl;
1489 f1 | rngfuncb
1490 -------------+----------------------------
1491 0 | (-1,0z)
1492 123456 | (61727,123456z)
1493 -123456 | (-61729,-123456z)
1494 2147483647 | (1073741822,2147483647z)
1495 -2147483647 | (-1073741824,-2147483647z)
1496 (5 rows)
1498 SELECT * FROM rngfuncb(42, 99);
1499 f2 | column2
1500 ----+---------
1501 98 | 42z
1502 (1 row)
1504 SELECT * FROM rngfuncb(42, 99) AS p(a,b);
1505 a | b
1506 ----+-----
1507 98 | 42z
1508 (1 row)
1510 -- Can reference function with or without OUT params for DROP, etc
1511 DROP FUNCTION rngfunc(int);
1512 DROP FUNCTION rngfuncr(in f2 int, out f1 int, out text);
1513 DROP FUNCTION rngfuncb(in f1 int, inout f2 int);
1514 --
1515 -- For my next trick, polymorphic OUT parameters
1516 --
1517 CREATE FUNCTION dup (f1 anyelement, f2 out anyelement, f3 out anyarray)
1518 AS 'select $1, array[$1,$1]' LANGUAGE sql;
1519 SELECT dup(22);
1520 dup
1521 ----------------
1522 (22,"{22,22}")
1523 (1 row)
1525 SELECT dup('xyz'); -- fails
1526 ERROR: could not determine polymorphic type because input has type unknown
1527 SELECT dup('xyz'::text);
1528 dup
1529 -------------------
1530 (xyz,"{xyz,xyz}")
1531 (1 row)
1533 SELECT * FROM dup('xyz'::text);
1534 f2 | f3
1535 -----+-----------
1536 xyz | {xyz,xyz}
1537 (1 row)
1539 -- fails, as we are attempting to rename first argument
1540 CREATE OR REPLACE FUNCTION dup (inout f2 anyelement, out f3 anyarray)
1541 AS 'select $1, array[$1,$1]' LANGUAGE sql;
1542 ERROR: cannot change name of input parameter "f1"
1543 HINT: Use DROP FUNCTION dup(anyelement) first.
1544 DROP FUNCTION dup(anyelement);
1545 -- equivalent behavior, though different name exposed for input arg
1546 CREATE OR REPLACE FUNCTION dup (inout f2 anyelement, out f3 anyarray)
1547 AS 'select $1, array[$1,$1]' LANGUAGE sql;
1548 SELECT dup(22);
1549 dup
1550 ----------------
1551 (22,"{22,22}")
1552 (1 row)
1554 DROP FUNCTION dup(anyelement);
1555 -- fails, no way to deduce outputs
1556 CREATE FUNCTION bad (f1 int, out f2 anyelement, out f3 anyarray)
1557 AS 'select $1, array[$1,$1]' LANGUAGE sql;
1558 ERROR: cannot determine result data type
1559 DETAIL: A result of type anyelement requires at least one input of type anyelement, anyarray, anynonarray, anyenum, anyrange, or anymultirange.
1560 CREATE FUNCTION dup (f1 anycompatible, f2 anycompatiblearray, f3 out anycompatible, f4 out anycompatiblearray)
1561 AS 'select $1, $2' LANGUAGE sql;
1562 SELECT dup(22, array[44]);
1563 dup
1564 -----------
1565 (22,{44})
1566 (1 row)
1568 SELECT dup(4.5, array[44]);
1569 dup
1570 ------------
1571 (4.5,{44})
1572 (1 row)
1574 SELECT dup(22, array[44::bigint]);
1575 dup
1576 -----------
1577 (22,{44})
1578 (1 row)
1580 SELECT *, pg_typeof(f3), pg_typeof(f4) FROM dup(22, array[44::bigint]);
1581 f3 | f4 | pg_typeof | pg_typeof
1582 ----+------+-----------+-----------
1583 22 | {44} | bigint | bigint[]
1584 (1 row)
1586 DROP FUNCTION dup(f1 anycompatible, f2 anycompatiblearray);
1587 CREATE FUNCTION dup (f1 anycompatiblerange, f2 out anycompatible, f3 out anycompatiblearray, f4 out anycompatiblerange)
1588 AS 'select lower($1), array[lower($1), upper($1)], $1' LANGUAGE sql;
1589 SELECT dup(int4range(4,7));
1590 dup
1591 ---------------------
1592 (4,"{4,7}","[4,7)")
1593 (1 row)
1595 SELECT dup(numrange(4,7));
1596 dup
1597 ---------------------
1598 (4,"{4,7}","[4,7)")
1599 (1 row)
1601 SELECT dup(textrange('aaa', 'bbb'));
1602 dup
1603 -------------------------------
1604 (aaa,"{aaa,bbb}","[aaa,bbb)")
1605 (1 row)
1607 DROP FUNCTION dup(f1 anycompatiblerange);
1608 -- fails, no way to deduce outputs
1609 CREATE FUNCTION bad (f1 anyarray, out f2 anycompatible, out f3 anycompatiblearray)
1610 AS 'select $1, array[$1,$1]' LANGUAGE sql;
1611 ERROR: cannot determine result data type
1612 DETAIL: A result of type anycompatible requires at least one input of type anycompatible, anycompatiblearray, anycompatiblenonarray, anycompatiblerange, or anycompatiblemultirange.
1613 --
1614 -- table functions
1615 --
1616 CREATE OR REPLACE FUNCTION rngfunc()
1617 RETURNS TABLE(a int)
1618 AS $$ SELECT a FROM generate_series(1,5) a(a) $$ LANGUAGE sql;
1619 SELECT * FROM rngfunc();
1620 a
1621 ---
1622 1
1623 2
1624 3
1625 4
1626 5
1627 (5 rows)
1629 DROP FUNCTION rngfunc();
1630 CREATE OR REPLACE FUNCTION rngfunc(int)
1631 RETURNS TABLE(a int, b int)
1632 AS $$ SELECT a, b
1633 FROM generate_series(1,$1) a(a),
1634 generate_series(1,$1) b(b) $$ LANGUAGE sql;
1635 SELECT * FROM rngfunc(3);
1636 a | b
1637 ---+---
1638 1 | 1
1639 1 | 2
1640 1 | 3
1641 2 | 1
1642 2 | 2
1643 2 | 3
1644 3 | 1
1645 3 | 2
1646 3 | 3
1647 (9 rows)
1649 DROP FUNCTION rngfunc(int);
1650 -- case that causes change of typmod knowledge during inlining
1651 CREATE OR REPLACE FUNCTION rngfunc()
1652 RETURNS TABLE(a varchar(5))
1653 AS $$ SELECT 'hello'::varchar(5) $$ LANGUAGE sql STABLE;
1654 SELECT * FROM rngfunc() GROUP BY 1;
1655 a
1656 -------
1657 hello
1658 (1 row)
1660 DROP FUNCTION rngfunc();
1661 --
1662 -- some tests on SQL functions with RETURNING
1663 --
1664 create temp table tt(f1 serial, data text);
1665 create function insert_tt(text) returns int as
1666 $$ insert into tt(data) values($1) returning f1 $$
1667 language sql;
1668 select insert_tt('foo');
1669 insert_tt
1670 -----------
1671 1
1672 (1 row)
1674 select insert_tt('bar');
1675 insert_tt
1676 -----------
1677 2
1678 (1 row)
1680 select * from tt;
1681 f1 | data
1682 ----+------
1683 1 | foo
1684 2 | bar
1685 (2 rows)
1687 -- insert will execute to completion even if function needs just 1 row
1688 create or replace function insert_tt(text) returns int as
1689 $$ insert into tt(data) values($1),($1||$1) returning f1 $$
1690 language sql;
1691 select insert_tt('fool');
1692 insert_tt
1693 -----------
1694 3
1695 (1 row)
1697 select * from tt;
1698 f1 | data
1699 ----+----------
1700 1 | foo
1701 2 | bar
1702 3 | fool
1703 4 | foolfool
1704 (4 rows)
1706 -- setof does what's expected
1707 create or replace function insert_tt2(text,text) returns setof int as
1708 $$ insert into tt(data) values($1),($2) returning f1 $$
1709 language sql;
1710 select insert_tt2('foolish','barrish');
1711 insert_tt2
1712 ------------
1713 5
1714 6
1715 (2 rows)
1717 select * from insert_tt2('baz','quux');
1718 insert_tt2
1719 ------------
1720 7
1721 8
1722 (2 rows)
1724 select * from tt;
1725 f1 | data
1726 ----+----------
1727 1 | foo
1728 2 | bar
1729 3 | fool
1730 4 | foolfool
1731 5 | foolish
1732 6 | barrish
1733 7 | baz
1734 8 | quux
1735 (8 rows)
1737 -- limit doesn't prevent execution to completion
1738 select insert_tt2('foolish','barrish') limit 1;
1739 insert_tt2
1740 ------------
1741 9
1742 (1 row)
1744 select * from tt;
1745 f1 | data
1746 ----+----------
1747 1 | foo
1748 2 | bar
1749 3 | fool
1750 4 | foolfool
1751 5 | foolish
1752 6 | barrish
1753 7 | baz
1754 8 | quux
1755 9 | foolish
1756 10 | barrish
1757 (10 rows)
1759 -- triggers will fire, too
1760 create function noticetrigger() returns trigger as $$
1761 begin
1762 raise notice 'noticetrigger % %', new.f1, new.data;
1763 return null;
1764 end $$ language plpgsql;
1765 create trigger tnoticetrigger after insert on tt for each row
1766 execute procedure noticetrigger();
1767 select insert_tt2('foolme','barme') limit 1;
1768 NOTICE: noticetrigger 11 foolme
1769 NOTICE: noticetrigger 12 barme
1770 insert_tt2
1771 ------------
1772 11
1773 (1 row)
1775 select * from tt;
1776 f1 | data
1777 ----+----------
1778 1 | foo
1779 2 | bar
1780 3 | fool
1781 4 | foolfool
1782 5 | foolish
1783 6 | barrish
1784 7 | baz
1785 8 | quux
1786 9 | foolish
1787 10 | barrish
1788 11 | foolme
1789 12 | barme
1790 (12 rows)
1792 -- and rules work
1793 create temp table tt_log(f1 int, data text);
1794 create rule insert_tt_rule as on insert to tt do also
1795 insert into tt_log values(new.*);
1796 select insert_tt2('foollog','barlog') limit 1;
1797 NOTICE: noticetrigger 13 foollog
1798 NOTICE: noticetrigger 14 barlog
1799 insert_tt2
1800 ------------
1801 13
1802 (1 row)
1804 select * from tt;
1805 f1 | data
1806 ----+----------
1807 1 | foo
1808 2 | bar
1809 3 | fool
1810 4 | foolfool
1811 5 | foolish
1812 6 | barrish
1813 7 | baz
1814 8 | quux
1815 9 | foolish
1816 10 | barrish
1817 11 | foolme
1818 12 | barme
1819 13 | foollog
1820 14 | barlog
1821 (14 rows)
1823 -- note that nextval() gets executed a second time in the rule expansion,
1824 -- which is expected.
1825 select * from tt_log;
1826 f1 | data
1827 ----+---------
1828 15 | foollog
1829 16 | barlog
1830 (2 rows)
1832 -- test case for a whole-row-variable bug
1833 create function rngfunc1(n integer, out a text, out b text)
1834 returns setof record
1835 language sql
1836 as $$ select 'foo ' || i, 'bar ' || i from generate_series(1,$1) i $$;
1837 set work_mem='64kB';
1838 select t.a, t, t.a from rngfunc1(10000) t limit 1;
1839 a | t | a
1840 -------+-------------------+-------
1841 foo 1 | ("foo 1","bar 1") | foo 1
1842 (1 row)
1844 reset work_mem;
1845 select t.a, t, t.a from rngfunc1(10000) t limit 1;
1846 a | t | a
1847 -------+-------------------+-------
1848 foo 1 | ("foo 1","bar 1") | foo 1
1849 (1 row)
1851 drop function rngfunc1(n integer);
1852 -- test use of SQL functions returning record
1853 -- this is supported in some cases where the query doesn't specify
1854 -- the actual record type ...
1855 create function array_to_set(anyarray) returns setof record as $$
1856 select i AS "index", $1[i] AS "value" from generate_subscripts($1, 1) i
1857 $$ language sql strict immutable;
1858 select array_to_set(array['one', 'two']);
1859 array_to_set
1860 --------------
1861 (1,one)
1862 (2,two)
1863 (2 rows)
1865 select * from array_to_set(array['one', 'two']) as t(f1 int,f2 text);
1866 f1 | f2
1867 ----+-----
1868 1 | one
1869 2 | two
1870 (2 rows)
1872 select * from array_to_set(array['one', 'two']); -- fail
1873 ERROR: a column definition list is required for functions returning "record"
1874 LINE 1: select * from array_to_set(array['one', 'two']);
1875 ^
1876 -- after-the-fact coercion of the columns is now possible, too
1877 select * from array_to_set(array['one', 'two']) as t(f1 numeric(4,2),f2 text);
1878 f1 | f2
1879 ------+-----
1880 1.00 | one
1881 2.00 | two
1882 (2 rows)
1884 -- and if it doesn't work, you get a compile-time not run-time error
1885 select * from array_to_set(array['one', 'two']) as t(f1 point,f2 text);
1886 ERROR: return type mismatch in function declared to return record
1887 DETAIL: Final statement returns integer instead of point at column 1.
1888 CONTEXT: SQL function "array_to_set" during startup
1889 -- with "strict", this function can't be inlined in FROM
1890 explain (verbose, costs off)
1891 select * from array_to_set(array['one', 'two']) as t(f1 numeric(4,2),f2 text);
1892 QUERY PLAN
1893 ----------------------------------------------------
1894 Function Scan on public.array_to_set t
1895 Output: f1, f2
1896 Function Call: array_to_set('{one,two}'::text[])
1897 (3 rows)
1899 -- but without, it can be:
1900 create or replace function array_to_set(anyarray) returns setof record as $$
1901 select i AS "index", $1[i] AS "value" from generate_subscripts($1, 1) i
1902 $$ language sql immutable;
1903 select array_to_set(array['one', 'two']);
1904 array_to_set
1905 --------------
1906 (1,one)
1907 (2,two)
1908 (2 rows)
1910 select * from array_to_set(array['one', 'two']) as t(f1 int,f2 text);
1911 f1 | f2
1912 ----+-----
1913 1 | one
1914 2 | two
1915 (2 rows)
1917 select * from array_to_set(array['one', 'two']) as t(f1 numeric(4,2),f2 text);
1918 f1 | f2
1919 ------+-----
1920 1.00 | one
1921 2.00 | two
1922 (2 rows)
1924 select * from array_to_set(array['one', 'two']) as t(f1 point,f2 text);
1925 ERROR: return type mismatch in function declared to return record
1926 DETAIL: Final statement returns integer instead of point at column 1.
1927 CONTEXT: SQL function "array_to_set" during inlining
1928 explain (verbose, costs off)
1929 select * from array_to_set(array['one', 'two']) as t(f1 numeric(4,2),f2 text);
1930 QUERY PLAN
1931 --------------------------------------------------------------
1932 Function Scan on pg_catalog.generate_subscripts i
1933 Output: i.i, ('{one,two}'::text[])[i.i]
1934 Function Call: generate_subscripts('{one,two}'::text[], 1)
1935 (3 rows)
1937 create temp table rngfunc(f1 int8, f2 int8);
1938 create function testrngfunc() returns record as $$
1939 insert into rngfunc values (1,2) returning *;
1940 $$ language sql;
1941 select testrngfunc();
1942 testrngfunc
1943 -------------
1944 (1,2)
1945 (1 row)
1947 select * from testrngfunc() as t(f1 int8,f2 int8);
1948 f1 | f2
1949 ----+----
1950 1 | 2
1951 (1 row)
1953 select * from testrngfunc(); -- fail
1954 ERROR: a column definition list is required for functions returning "record"
1955 LINE 1: select * from testrngfunc();
1956 ^
1957 drop function testrngfunc();
1958 create function testrngfunc() returns setof record as $$
1959 insert into rngfunc values (1,2), (3,4) returning *;
1960 $$ language sql;
1961 select testrngfunc();
1962 testrngfunc
1963 -------------
1964 (1,2)
1965 (3,4)
1966 (2 rows)
1968 select * from testrngfunc() as t(f1 int8,f2 int8);
1969 f1 | f2
1970 ----+----
1971 1 | 2
1972 3 | 4
1973 (2 rows)
1975 select * from testrngfunc(); -- fail
1976 ERROR: a column definition list is required for functions returning "record"
1977 LINE 1: select * from testrngfunc();
1978 ^
1979 drop function testrngfunc();
1980 -- Check that typmod imposed by a composite type is honored
1981 create type rngfunc_type as (f1 numeric(35,6), f2 numeric(35,2));
1982 create function testrngfunc() returns rngfunc_type as $$
1983 select 7.136178319899999964, 7.136178319899999964;
1984 $$ language sql immutable;
1985 explain (verbose, costs off)
1986 select testrngfunc();
1987 QUERY PLAN
1988 -------------------------------------------
1989 Result
1990 Output: '(7.136178,7.14)'::rngfunc_type
1991 (2 rows)
1993 select testrngfunc();
1994 testrngfunc
1995 -----------------
1996 (7.136178,7.14)
1997 (1 row)
1999 explain (verbose, costs off)
2000 select * from testrngfunc();
2001 QUERY PLAN
2002 --------------------------------------------------
2003 Function Scan on testrngfunc
2004 Output: f1, f2
2005 Function Call: '(7.136178,7.14)'::rngfunc_type
2006 (3 rows)
2008 select * from testrngfunc();
2009 f1 | f2
2010 ----------+------
2011 7.136178 | 7.14
2012 (1 row)
2014 create or replace function testrngfunc() returns rngfunc_type as $$
2015 select 7.136178319899999964, 7.136178319899999964;
2016 $$ language sql volatile;
2017 explain (verbose, costs off)
2018 select testrngfunc();
2019 QUERY PLAN
2020 -------------------------
2021 Result
2022 Output: testrngfunc()
2023 (2 rows)
2025 select testrngfunc();
2026 testrngfunc
2027 -----------------
2028 (7.136178,7.14)
2029 (1 row)
2031 explain (verbose, costs off)
2032 select * from testrngfunc();
2033 QUERY PLAN
2034 -------------------------------------
2035 Function Scan on public.testrngfunc
2036 Output: f1, f2
2037 Function Call: testrngfunc()
2038 (3 rows)
2040 select * from testrngfunc();
2041 f1 | f2
2042 ----------+------
2043 7.136178 | 7.14
2044 (1 row)
2046 drop function testrngfunc();
2047 create function testrngfunc() returns setof rngfunc_type as $$
2048 select 7.136178319899999964, 7.136178319899999964;
2049 $$ language sql immutable;
2050 explain (verbose, costs off)
2051 select testrngfunc();
2052 QUERY PLAN
2053 -------------------------
2054 ProjectSet
2055 Output: testrngfunc()
2056 -> Result
2057 (3 rows)
2059 select testrngfunc();
2060 testrngfunc
2061 -----------------
2062 (7.136178,7.14)
2063 (1 row)
2065 explain (verbose, costs off)
2066 select * from testrngfunc();
2067 QUERY PLAN
2068 --------------------------------------------------------
2069 Result
2070 Output: 7.136178::numeric(35,6), 7.14::numeric(35,2)
2071 (2 rows)
2073 select * from testrngfunc();
2074 f1 | f2
2075 ----------+------
2076 7.136178 | 7.14
2077 (1 row)
2079 create or replace function testrngfunc() returns setof rngfunc_type as $$
2080 select 7.136178319899999964, 7.136178319899999964;
2081 $$ language sql volatile;
2082 explain (verbose, costs off)
2083 select testrngfunc();
2084 QUERY PLAN
2085 -------------------------
2086 ProjectSet
2087 Output: testrngfunc()
2088 -> Result
2089 (3 rows)
2091 select testrngfunc();
2092 testrngfunc
2093 -----------------
2094 (7.136178,7.14)
2095 (1 row)
2097 explain (verbose, costs off)
2098 select * from testrngfunc();
2099 QUERY PLAN
2100 -------------------------------------
2101 Function Scan on public.testrngfunc
2102 Output: f1, f2
2103 Function Call: testrngfunc()
2104 (3 rows)
2106 select * from testrngfunc();
2107 f1 | f2
2108 ----------+------
2109 7.136178 | 7.14
2110 (1 row)
2112 create or replace function testrngfunc() returns setof rngfunc_type as $$
2113 select 1, 2 union select 3, 4 order by 1;
2114 $$ language sql immutable;
2115 explain (verbose, costs off)
2116 select testrngfunc();
2117 QUERY PLAN
2118 -------------------------
2119 ProjectSet
2120 Output: testrngfunc()
2121 -> Result
2122 (3 rows)
2124 select testrngfunc();
2125 testrngfunc
2126 -----------------
2127 (1.000000,2.00)
2128 (3.000000,4.00)
2129 (2 rows)
2131 explain (verbose, costs off)
2132 select * from testrngfunc();
2133 QUERY PLAN
2134 ----------------------------------------------------------
2135 Subquery Scan on "*SELECT*"
2136 Output: "*SELECT*"."?column?", "*SELECT*"."?column?_1"
2137 -> Unique
2138 Output: (1), (2)
2139 -> Sort
2140 Output: (1), (2)
2141 Sort Key: (1), (2)
2142 -> Append
2143 -> Result
2144 Output: 1, 2
2145 -> Result
2146 Output: 3, 4
2147 (12 rows)
2149 select * from testrngfunc();
2150 f1 | f2
2151 ----------+------
2152 1.000000 | 2.00
2153 3.000000 | 4.00
2154 (2 rows)
2156 -- Check a couple of error cases while we're here
2157 select * from testrngfunc() as t(f1 int8,f2 int8); -- fail, composite result
2158 ERROR: a column definition list is redundant for a function returning a named composite type
2159 LINE 1: select * from testrngfunc() as t(f1 int8,f2 int8);
2160 ^
2161 select * from pg_get_keywords() as t(f1 int8,f2 int8); -- fail, OUT params
2162 ERROR: a column definition list is redundant for a function with OUT parameters
2163 LINE 1: select * from pg_get_keywords() as t(f1 int8,f2 int8);
2164 ^
2165 select * from sin(3) as t(f1 int8,f2 int8); -- fail, scalar result type
2166 ERROR: a column definition list is only allowed for functions returning "record"
2167 LINE 1: select * from sin(3) as t(f1 int8,f2 int8);
2168 ^
2169 drop type rngfunc_type cascade;
2170 NOTICE: drop cascades to function testrngfunc()
2171 --
2172 -- Check some cases involving added/dropped columns in a rowtype result
2173 --
2174 create temp table users (userid text, seq int, email text, todrop bool, moredrop int, enabled bool);
2175 insert into users values ('id',1,'email',true,11,true);
2176 insert into users values ('id2',2,'email2',true,12,true);
2177 alter table users drop column todrop;
2178 create or replace function get_first_user() returns users as
2179 $$ SELECT * FROM users ORDER BY userid LIMIT 1; $$
2180 language sql stable;
2181 SELECT get_first_user();
2182 get_first_user
2183 -------------------
2184 (id,1,email,11,t)
2185 (1 row)
2187 SELECT * FROM get_first_user();
2188 userid | seq | email | moredrop | enabled
2189 --------+-----+-------+----------+---------
2190 id | 1 | email | 11 | t
2191 (1 row)
2193 create or replace function get_users() returns setof users as
2194 $$ SELECT * FROM users ORDER BY userid; $$
2195 language sql stable;
2196 SELECT get_users();
2197 get_users
2198 ---------------------
2199 (id,1,email,11,t)
2200 (id2,2,email2,12,t)
2201 (2 rows)
2203 SELECT * FROM get_users();
2204 userid | seq | email | moredrop | enabled
2205 --------+-----+--------+----------+---------
2206 id | 1 | email | 11 | t
2207 id2 | 2 | email2 | 12 | t
2208 (2 rows)
2210 SELECT * FROM get_users() WITH ORDINALITY; -- make sure ordinality copes
2211 userid | seq | email | moredrop | enabled | ordinality
2212 --------+-----+--------+----------+---------+------------
2213 id | 1 | email | 11 | t | 1
2214 id2 | 2 | email2 | 12 | t | 2
2215 (2 rows)
2217 -- multiple functions vs. dropped columns
2218 SELECT * FROM ROWS FROM(generate_series(10,11), get_users()) WITH ORDINALITY;
2219 generate_series | userid | seq | email | moredrop | enabled | ordinality
2220 -----------------+--------+-----+--------+----------+---------+------------
2221 10 | id | 1 | email | 11 | t | 1
2222 11 | id2 | 2 | email2 | 12 | t | 2
2223 (2 rows)
2225 SELECT * FROM ROWS FROM(get_users(), generate_series(10,11)) WITH ORDINALITY;
2226 userid | seq | email | moredrop | enabled | generate_series | ordinality
2227 --------+-----+--------+----------+---------+-----------------+------------
2228 id | 1 | email | 11 | t | 10 | 1
2229 id2 | 2 | email2 | 12 | t | 11 | 2
2230 (2 rows)
2232 -- check that we can cope with post-parsing changes in rowtypes
2233 create temp view usersview as
2234 SELECT * FROM ROWS FROM(get_users(), generate_series(10,11)) WITH ORDINALITY;
2235 select * from usersview;
2236 userid | seq | email | moredrop | enabled | generate_series | ordinality
2237 --------+-----+--------+----------+---------+-----------------+------------
2238 id | 1 | email | 11 | t | 10 | 1
2239 id2 | 2 | email2 | 12 | t | 11 | 2
2240 (2 rows)
2242 alter table users add column junk text;
2243 select * from usersview;
2244 userid | seq | email | moredrop | enabled | generate_series | ordinality
2245 --------+-----+--------+----------+---------+-----------------+------------
2246 id | 1 | email | 11 | t | 10 | 1
2247 id2 | 2 | email2 | 12 | t | 11 | 2
2248 (2 rows)
2250 alter table users drop column moredrop; -- fail, view has reference
2251 ERROR: cannot drop column moredrop of table users because other objects depend on it
2252 DETAIL: view usersview depends on column moredrop of table users
2253 HINT: Use DROP ... CASCADE to drop the dependent objects too.
2254 -- We used to have a bug that would allow the above to succeed, posing
2255 -- hazards for later execution of the view. Check that the internal
2256 -- defenses for those hazards haven't bit-rotted, in case some other
2257 -- bug with similar symptoms emerges.
2258 begin;
2259 -- destroy the dependency entry that prevents the DROP:
2260 delete from pg_depend where
2261 objid = (select oid from pg_rewrite
2262 where ev_class = 'usersview'::regclass and rulename = '_RETURN')
2263 and refobjsubid = 5
2264 returning pg_describe_object(classid, objid, objsubid) as obj,
2265 pg_describe_object(refclassid, refobjid, refobjsubid) as ref,
2266 deptype;
2267 obj | ref | deptype
2268 --------------------------------+--------------------------------+---------
2269 rule _RETURN on view usersview | column moredrop of table users | n
2270 (1 row)
2272 alter table users drop column moredrop;
2273 select * from usersview; -- expect clean failure
2274 ERROR: attribute 5 of type record has been dropped
2275 rollback;
2276 alter table users alter column seq type numeric; -- fail, view has reference
2277 ERROR: cannot alter type of a column used by a view or rule
2278 DETAIL: rule _RETURN on view usersview depends on column "seq"
2279 -- likewise, check we don't crash if the dependency goes wrong
2280 begin;
2281 -- destroy the dependency entry that prevents the ALTER:
2282 delete from pg_depend where
2283 objid = (select oid from pg_rewrite
2284 where ev_class = 'usersview'::regclass and rulename = '_RETURN')
2285 and refobjsubid = 2
2286 returning pg_describe_object(classid, objid, objsubid) as obj,
2287 pg_describe_object(refclassid, refobjid, refobjsubid) as ref,
2288 deptype;
2289 obj | ref | deptype
2290 --------------------------------+---------------------------+---------
2291 rule _RETURN on view usersview | column seq of table users | n
2292 (1 row)
2294 alter table users alter column seq type numeric;
2295 select * from usersview; -- expect clean failure
2296 ERROR: attribute 2 of type record has wrong type
2297 DETAIL: Table has type numeric, but query expects integer.
2298 rollback;
2299 drop view usersview;
2300 drop function get_first_user();
2301 drop function get_users();
2302 drop table users;
2303 -- check behavior with type coercion required for a set-op
2304 create or replace function rngfuncbar() returns setof text as
2305 $$ select 'foo'::varchar union all select 'bar'::varchar ; $$
2306 language sql stable;
2307 select rngfuncbar();
2308 rngfuncbar
2309 ------------
2310 foo
2311 bar
2312 (2 rows)
2314 select * from rngfuncbar();
2315 rngfuncbar
2316 ------------
2317 foo
2318 bar
2319 (2 rows)
2321 -- this function is now inlinable, too:
2322 explain (verbose, costs off) select * from rngfuncbar();
2323 QUERY PLAN
2324 ------------------------------------------------
2325 Result
2326 Output: ('foo'::character varying)
2327 -> Append
2328 -> Result
2329 Output: 'foo'::character varying
2330 -> Result
2331 Output: 'bar'::character varying
2332 (7 rows)
2334 drop function rngfuncbar();
2335 -- check handling of a SQL function with multiple OUT params (bug #5777)
2336 create or replace function rngfuncbar(out integer, out numeric) as
2337 $$ select (1, 2.1) $$ language sql;
2338 select * from rngfuncbar();
2339 column1 | column2
2340 ---------+---------
2341 1 | 2.1
2342 (1 row)
2344 create or replace function rngfuncbar(out integer, out numeric) as
2345 $$ select (1, 2) $$ language sql;
2346 select * from rngfuncbar(); -- fail
2347 ERROR: function return row and query-specified return row do not match
2348 DETAIL: Returned type integer at ordinal position 2, but query expects numeric.
2349 create or replace function rngfuncbar(out integer, out numeric) as
2350 $$ select (1, 2.1, 3) $$ language sql;
2351 select * from rngfuncbar(); -- fail
2352 ERROR: function return row and query-specified return row do not match
2353 DETAIL: Returned row contains 3 attributes, but query expects 2.
2354 drop function rngfuncbar();
2355 -- check whole-row-Var handling in nested lateral functions (bug #11703)
2356 create function extractq2(t int8_tbl) returns int8 as $$
2357 select t.q2
2358 $$ language sql immutable;
2359 explain (verbose, costs off)
2360 select x from int8_tbl, extractq2(int8_tbl) f(x);
2361 QUERY PLAN
2362 ------------------------------------------
2363 Nested Loop
2364 Output: f.x
2365 -> Seq Scan on public.int8_tbl
2366 Output: int8_tbl.q1, int8_tbl.q2
2367 -> Function Scan on f
2368 Output: f.x
2369 Function Call: int8_tbl.q2
2370 (7 rows)
2372 select x from int8_tbl, extractq2(int8_tbl) f(x);
2373 x
2374 -------------------
2375 456
2376 4567890123456789
2377 123
2378 4567890123456789
2379 -4567890123456789
2380 (5 rows)
2382 create function extractq2_2(t int8_tbl) returns table(ret1 int8) as $$
2383 select extractq2(t) offset 0
2384 $$ language sql immutable;
2385 explain (verbose, costs off)
2386 select x from int8_tbl, extractq2_2(int8_tbl) f(x);
2387 QUERY PLAN
2388 -----------------------------------
2389 Nested Loop
2390 Output: ((int8_tbl.*).q2)
2391 -> Seq Scan on public.int8_tbl
2392 Output: int8_tbl.*
2393 -> Result
2394 Output: (int8_tbl.*).q2
2395 (6 rows)
2397 select x from int8_tbl, extractq2_2(int8_tbl) f(x);
2398 x
2399 -------------------
2400 456
2401 4567890123456789
2402 123
2403 4567890123456789
2404 -4567890123456789
2405 (5 rows)
2407 -- without the "offset 0", this function gets optimized quite differently
2408 create function extractq2_2_opt(t int8_tbl) returns table(ret1 int8) as $$
2409 select extractq2(t)
2410 $$ language sql immutable;
2411 explain (verbose, costs off)
2412 select x from int8_tbl, extractq2_2_opt(int8_tbl) f(x);
2413 QUERY PLAN
2414 -----------------------------
2415 Seq Scan on public.int8_tbl
2416 Output: int8_tbl.q2
2417 (2 rows)
2419 select x from int8_tbl, extractq2_2_opt(int8_tbl) f(x);
2420 x
2421 -------------------
2422 456
2423 4567890123456789
2424 123
2425 4567890123456789
2426 -4567890123456789
2427 (5 rows)
2429 -- check handling of nulls in SRF results (bug #7808)
2430 create type rngfunc2 as (a integer, b text);
2431 select *, row_to_json(u) from unnest(array[(1,'foo')::rngfunc2, null::rngfunc2]) u;
2432 a | b | row_to_json
2433 ---+-----+---------------------
2434 1 | foo | {"a":1,"b":"foo"}
2435 | | {"a":null,"b":null}
2436 (2 rows)
2438 select *, row_to_json(u) from unnest(array[null::rngfunc2, null::rngfunc2]) u;
2439 a | b | row_to_json
2440 ---+---+---------------------
2441 | | {"a":null,"b":null}
2442 | | {"a":null,"b":null}
2443 (2 rows)
2445 select *, row_to_json(u) from unnest(array[null::rngfunc2, (1,'foo')::rngfunc2, null::rngfunc2]) u;
2446 a | b | row_to_json
2447 ---+-----+---------------------
2448 | | {"a":null,"b":null}
2449 1 | foo | {"a":1,"b":"foo"}
2450 | | {"a":null,"b":null}
2451 (3 rows)
2453 select *, row_to_json(u) from unnest(array[]::rngfunc2[]) u;
2454 a | b | row_to_json
2455 ---+---+-------------
2456 (0 rows)
2458 drop type rngfunc2;
2459 -- check handling of functions pulled up into function RTEs (bug #17227)
2460 explain (verbose, costs off)
2461 select * from
2462 (select jsonb_path_query_array(module->'lectures', '$[*]') as lecture
2463 from unnest(array['{"lectures": [{"id": "1"}]}'::jsonb])
2464 as unnested_modules(module)) as ss,
2465 jsonb_to_recordset(ss.lecture) as j (id text);
2466 QUERY PLAN
2467 --------------------------------------------------------------------------------------------------------------------------------------------------------
2468 Nested Loop
2469 Output: jsonb_path_query_array((unnested_modules.module -> 'lectures'::text), '$[*]'::jsonpath, '{}'::jsonb, false), j.id
2470 -> Function Scan on pg_catalog.unnest unnested_modules
2471 Output: unnested_modules.module
2472 Function Call: unnest('{"{\"lectures\": [{\"id\": \"1\"}]}"}'::jsonb[])
2473 -> Function Scan on pg_catalog.jsonb_to_recordset j
2474 Output: j.id
2475 Function Call: jsonb_to_recordset(jsonb_path_query_array((unnested_modules.module -> 'lectures'::text), '$[*]'::jsonpath, '{}'::jsonb, false))
2476 (8 rows)
2478 select * from
2479 (select jsonb_path_query_array(module->'lectures', '$[*]') as lecture
2480 from unnest(array['{"lectures": [{"id": "1"}]}'::jsonb])
2481 as unnested_modules(module)) as ss,
2482 jsonb_to_recordset(ss.lecture) as j (id text);
2483 lecture | id
2484 ---------------+----
2485 [{"id": "1"}] | 1
2486 (1 row)
2488 -- check detection of mismatching record types with a const-folded expression
2489 with a(b) as (values (row(1,2,3)))
2490 select * from a, coalesce(b) as c(d int, e int); -- fail
2491 ERROR: function return row and query-specified return row do not match
2492 DETAIL: Returned row contains 3 attributes, but query expects 2.
2493 with a(b) as (values (row(1,2,3)))
2494 select * from a, coalesce(b) as c(d int, e int, f int, g int); -- fail
2495 ERROR: function return row and query-specified return row do not match
2496 DETAIL: Returned row contains 3 attributes, but query expects 4.
2497 with a(b) as (values (row(1,2,3)))
2498 select * from a, coalesce(b) as c(d int, e int, f float); -- fail
2499 ERROR: function return row and query-specified return row do not match
2500 DETAIL: Returned type integer at ordinal position 3, but query expects double precision.
2501 select * from int8_tbl, coalesce(row(1)) as (a int, b int); -- fail
2502 ERROR: function return row and query-specified return row do not match
2503 DETAIL: Returned row contains 1 attribute, but query expects 2.