Hi! I have found executable examples that do not work correctly in the doc of "SQL Functions Returning Sets" in xfunc.sgml. So I fixed the examples as follows. - Changed CREATE TABLE tab '(y int, z int)' to '(x int, y int, z int)' - Changed INSERT INTO tab '(1, 2), (3, 4), (5, 6), (7, 8)' to '(1, 2, 3), (4, 5, 6), (7, 8, 9), (10, 11, 12)' - Changed CREATE FUNCTION sum_n_product_with_tab '(x int, OUT sum int, OUT product int)' to '(int, OUT sum int, OUT product int)' - Changed CREATE FUNCTION sum_n_product_with_tab 'SELECT $1 + tab.y, $1 * tab.y FROM tab;' to 'SELECT $1 + tab.x, $1 * tab.x FROM tab;' - Changed result of "SELECT * FROM sum_n_product_with_tab(10);"
The above will improve the results of examples as follows in this chapter.
①
- before
=# SELECT x, generate_series(1,5) AS g FROM tab;
ERROR: column "x" does not exist
LINE 1: SELECT x, generate_series(1,5) AS g FROM tab;
^
- after
=# SELECT x, generate_series(1,5) AS g FROM tab;
x | g
----+---
1 | 1
1 | 2
1 | 3
1 | 4
1 | 5
4 | 1
4 | 2
4 | 3
4 | 4
4 | 5
7 | 1
7 | 2
7 | 3
7 | 4
7 | 5
10 | 1
10 | 2
10 | 3
10 | 4
10 | 5
(20 rows)
②
- before
=# SELECT x, g FROM tab, LATERAL generate_series(1,5) AS g;
ERROR: column "x" does not exist
LINE 1: SELECT x, g FROM tab, LATERAL generate_series(1,5) AS g;
^
- after
=# SELECT x, g FROM tab, LATERAL generate_series(1,5) AS g;
x | g
----+---
1 | 1
4 | 1
7 | 1
10 | 1
1 | 2
4 | 2
7 | 2
10 | 2
1 | 3
4 | 3
7 | 3
10 | 3
1 | 4
4 | 4
7 | 4
10 | 4
1 | 5
4 | 5
7 | 5
10 | 5
(20 rows)
③
- before
=# SELECT srf1(srf2(x), srf3(y)), srf4(srf5(z)) FROM tab;
ERROR: column "x" does not exist
LINE 1: SELECT srf1(srf2(x), srf3(y)), srf4(srf5(z)) FROM tab;
^
- after
=# SELECT srf1(srf2(x), srf3(y)), srf4(srf5(z)) FROM tab;
ERROR: function srf2(integer) does not exist
LINE 1: SELECT srf1(srf2(x), srf3(y)), srf4(srf5(z)) FROM tab;
^
HINT: No function matches the given name and argument types. You
might need to add explicit type casts.
④
- before
=# SELECT x, CASE WHEN x > 0 THEN generate_series(1, 5) ELSE 0 END FROM tab;
ERROR: column "x" does not exist
LINE 1: SELECT x, CASE WHEN x > 0 THEN generate_series(1, 5) ELSE 0 ...
^
- after
=# SELECT x, CASE WHEN x > 0 THEN generate_series(1, 5) ELSE 0 END FROM tab;
ERROR: set-returning functions are not allowed in CASE
LINE 1: SELECT x, CASE WHEN x > 0 THEN generate_series(1, 5) ELSE 0 ...
^
HINT: You might be able to move the set-returning function into a
LATERAL FROM item.
⑤
- before
=# SELECT x, CASE WHEN y > 0 THEN generate_series(1, z) ELSE 5 END FROM tab;
ERROR: column "x" does not exist
LINE 1: SELECT x, CASE WHEN y > 0 THEN generate_series(1, z) ELSE 5 ...
^
- after
=# SELECT x, CASE WHEN y > 0 THEN generate_series(1, z) ELSE 5 END FROM tab;
ERROR: set-returning functions are not allowed in CASE
LINE 1: SELECT x, CASE WHEN y > 0 THEN generate_series(1, z) ELSE 5 ...
^
HINT: You might be able to move the set-returning function into a
LATERAL FROM item.
⑥
- before
=# SELECT x, case_generate_series(y > 0, 1, z, 5) FROM tab;
CREATE FUNCTION
ERROR: column "x" does not exist
LINE 1: SELECT x, case_generate_series(y > 0, 1, z, 5) FROM tab;
^
- after
=# SELECT x, case_generate_series(y > 0, 1, z, 5) FROM tab;
x | case_generate_series
----+----------------------
1 | 1
1 | 2
1 | 3
4 | 1
4 | 2
4 | 3
4 | 4
4 | 5
4 | 6
7 | 1
7 | 2
7 | 3
7 | 4
7 | 5
7 | 6
7 | 7
7 | 8
7 | 9
10 | 1
10 | 2
10 | 3
10 | 4
10 | 5
10 | 6
10 | 7
10 | 8
10 | 9
10 | 10
10 | 11
10 | 12
(30 rows)
Do you think?
Regards,
Mitsuru Hinata
NTT Open Source Software Center
0001-fix-SQL-Functions-Returning-Sets-docs-in-xfunc.sgml.patch
Description: Binary data
