Thread: A minor bug in the doc of "SQL Functions Returning Sets" in xfunc.sgml.

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

On Fri, Jul 19, 2024 at 10:46:04AM +0900, 日向充 wrote:
> 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.
>
> Do you think?

Not sure that this is worth changing.  The examples work OK when taken
in isolation or are able to demonstrate the point they want to show.
In short, not all these queries are here to be compatible with the
contents in the same area.  See for example the case of the "nodes"
table on the same page, created nowhere.  "tab" is just a more generic
table name that's more spread.
--
Michael

"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Thu, Jul 18, 2024 at 7:10 PM Michael Paquier <michael@paquier.xyz> wrote:
>> Not sure that this is worth changing.  The examples work OK when taken
>> in isolation or are able to demonstrate the point they want to show.
>> In short, not all these queries are here to be compatible with the
>> contents in the same area.  See for example the case of the "nodes"
>> table on the same page, created nowhere.  "tab" is just a more generic
>> table name that's more spread.

> Clearly this page repeatedly expects tab.x to exist; and for these queries
> to be executable.  This seems like the least invasive way to make that
> expectation reality.

I'm with Michael here.  Only in the tutorial do we expect there to be
a continuing thread of commands that you can just copy-and-paste and
expect to work.  I do not think it's reasonable to extend that policy
to the rest of the manual: in other places, there are too many
distinct topics under consideration and too much reason to make
localized changes.

            regards, tom lane

On Thu, Jul 18, 2024 at 9:05 PM Tom Lane
<tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> "David G. Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> writes:
> > On Thu, Jul 18, 2024 at 7:10 PM Michael Paquier <michael(at)paquier(dot)xyz>
> wrote:
> >> Not sure that this is worth changing.  The examples work OK when taken
> >> in isolation or are able to demonstrate the point they want to show.
> >> In short, not all these queries are here to be compatible with the
> >> contents in the same area.  See for example the case of the "nodes"
> >> table on the same page, created nowhere.  "tab" is just a more generic
> >> table name that's more spread.
>
> > Clearly this page repeatedly expects tab.x to exist; and for these
> queries
> > to be executable.  This seems like the least invasive way to make that
> > expectation reality.
>
> I'm with Michael here.  Only in the tutorial do we expect there to be
> a continuing thread of commands that you can just copy-and-paste and
> expect to work.  I do not think it's reasonable to extend that policy
> to the rest of the manual: in other places, there are too many
> distinct topics under consideration and too much reason to make
> localized changes.
>

Thank you Michael, David and Tom Lane for your replies.
I understand the policy in this document.

Mitsuru Hinata
NTT Open Source Software Center

2024年7月19日(金) 13:15 David G. Johnston <david.g.johnston@gmail.com>:
>
> On Thu, Jul 18, 2024 at 9:05 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>>
>> "David G. Johnston" <david.g.johnston@gmail.com> writes:
>> > On Thu, Jul 18, 2024 at 7:10 PM Michael Paquier <michael@paquier.xyz> wrote:
>> >> Not sure that this is worth changing.  The examples work OK when taken
>> >> in isolation or are able to demonstrate the point they want to show.
>> >> In short, not all these queries are here to be compatible with the
>> >> contents in the same area.  See for example the case of the "nodes"
>> >> table on the same page, created nowhere.  "tab" is just a more generic
>> >> table name that's more spread.
>>
>> > Clearly this page repeatedly expects tab.x to exist; and for these queries
>> > to be executable.  This seems like the least invasive way to make that
>> > expectation reality.
>>
>> I'm with Michael here.  Only in the tutorial do we expect there to be
>> a continuing thread of commands that you can just copy-and-paste and
>> expect to work.  I do not think it's reasonable to extend that policy
>> to the rest of the manual: in other places, there are too many
>> distinct topics under consideration and too much reason to make
>> localized changes.
>>
>
> So much for leaving a place a little nicer when you leave than when you arrived...
>
> David J.
>