pg.ddx.io pgsql-docs@postgresql.org mailing list archive
help / color / mirror / Atom feedA minor bug in the doc of "SQL Functions Returning Sets" in xfunc.sgml.
6+ messages / 4 participants
[nested] [flat]
* A minor bug in the doc of "SQL Functions Returning Sets" in xfunc.sgml.
@ 2024-07-19 01:46 日向充 <mitsuru.hinata.5432@gmail.com>
2024-07-19 02:10 ` Re: A minor bug in the doc of "SQL Functions Returning Sets" in xfunc.sgml. Michael Paquier <michael@paquier.xyz>
0 siblings, 1 reply; 6+ messages in thread
From: 日向充 @ 2024-07-19 01:46 UTC (permalink / raw)
To: pgsql-docs@lists.postgresql.org
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
Attachments:
[application/octet-stream] 0001-fix-SQL-Functions-Returning-Sets-docs-in-xfunc.sgml.patch (1.3K, ../../CAF-2iM_9-2a8PijHoHbS6ZFFV=tziOx8TDo4j+YGDAbsTApk4w@mail.gmail.com/2-0001-fix-SQL-Functions-Returning-Sets-docs-in-xfunc.sgml.patch)
download | inline diff:
From baaab149b10a2dd47fa3c4c5544468b10a3d87ec Mon Sep 17 00:00:00 2001
From: Mitsuru Hinata <mitsuru.hinata.5432@gmail.com>
Date: Thu, 18 Jul 2024 15:12:37 +0900
Subject: [PATCH] fix "SQL Functions Returning Sets" docs in xfunc.sgml
---
doc/src/sgml/xfunc.sgml | 12 ++++++------
1 file changed, 6 insertions(+), 6 deletions(-)
diff --git a/doc/src/sgml/xfunc.sgml b/doc/src/sgml/xfunc.sgml
index 7e92e89846..3adbc7a19d 100644
--- a/doc/src/sgml/xfunc.sgml
+++ b/doc/src/sgml/xfunc.sgml
@@ -1056,22 +1056,22 @@ SELECT * FROM getfoo(1) AS t1;
output parameters, like this:
<programlisting>
-CREATE TABLE tab (y int, z int);
-INSERT INTO tab VALUES (1, 2), (3, 4), (5, 6), (7, 8);
+CREATE TABLE tab (x int, y int, z int);
+INSERT INTO tab VALUES (1, 2, 3), (4, 5, 6), (7, 8, 9), (10, 11, 12);
-CREATE FUNCTION sum_n_product_with_tab (x int, OUT sum int, OUT product int)
+CREATE FUNCTION sum_n_product_with_tab (int, OUT sum int, OUT product int)
RETURNS SETOF record
AS $$
- SELECT $1 + tab.y, $1 * tab.y FROM tab;
+ SELECT $1 + tab.x, $1 * tab.x FROM tab;
$$ LANGUAGE SQL;
SELECT * FROM sum_n_product_with_tab(10);
sum | product
-----+---------
11 | 10
- 13 | 30
- 15 | 50
+ 14 | 40
17 | 70
+ 20 | 100
(4 rows)
</programlisting>
--
2.43.5
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: A minor bug in the doc of "SQL Functions Returning Sets" in xfunc.sgml.
2024-07-19 01:46 A minor bug in the doc of "SQL Functions Returning Sets" in xfunc.sgml. 日向充 <mitsuru.hinata.5432@gmail.com>
@ 2024-07-19 02:10 ` Michael Paquier <michael@paquier.xyz>
2024-07-19 03:55 ` Re: A minor bug in the doc of "SQL Functions Returning Sets" in xfunc.sgml. David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Michael Paquier @ 2024-07-19 02:10 UTC (permalink / raw)
To: 日向充 <mitsuru.hinata.5432@gmail.com>; +Cc: pgsql-docs@lists.postgresql.org
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
Attachments:
[application/pgp-signature] signature.asc (832B, ../../ZpnLEUUNxy-LP_lY@paquier.xyz/2-signature.asc)
download
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: A minor bug in the doc of "SQL Functions Returning Sets" in xfunc.sgml.
2024-07-19 01:46 A minor bug in the doc of "SQL Functions Returning Sets" in xfunc.sgml. 日向充 <mitsuru.hinata.5432@gmail.com>
2024-07-19 02:10 ` Re: A minor bug in the doc of "SQL Functions Returning Sets" in xfunc.sgml. Michael Paquier <michael@paquier.xyz>
@ 2024-07-19 03:55 ` David G. Johnston <david.g.johnston@gmail.com>
2024-07-19 04:05 ` Re: A minor bug in the doc of "SQL Functions Returning Sets" in xfunc.sgml. Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 6+ messages in thread
From: David G. Johnston @ 2024-07-19 03:55 UTC (permalink / raw)
To: Michael Paquier <michael@paquier.xyz>; +Cc: 日向充 <mitsuru.hinata.5432@gmail.com>; pgsql-docs@lists.postgresql.org
On Thu, Jul 18, 2024 at 7:10 PM Michael Paquier <michael@paquier.xyz> wrote:
> 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.
>
>
The attached patch is much more readable...
>
> > 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.
>
>
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. The extremely limited extent of nodes compared to tab
on this page doesn't support using it as a reason to not make the tab
examples work. If anything we should add a create table for nodes for the
reader like we did for tab.
I'd fixup [1] as to match, removing the name of the input parameter "x"
since we use $1 anyway. Getting rid of that now obsolete construction is a
whole other patch.
https://www.postgresql.org/docs/devel/xfunc-sql.html#XFUNC-SQL-FUNCTIONS-RETURNING-TABLE
David J.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: A minor bug in the doc of "SQL Functions Returning Sets" in xfunc.sgml.
2024-07-19 01:46 A minor bug in the doc of "SQL Functions Returning Sets" in xfunc.sgml. 日向充 <mitsuru.hinata.5432@gmail.com>
2024-07-19 02:10 ` Re: A minor bug in the doc of "SQL Functions Returning Sets" in xfunc.sgml. Michael Paquier <michael@paquier.xyz>
2024-07-19 03:55 ` Re: A minor bug in the doc of "SQL Functions Returning Sets" in xfunc.sgml. David G. Johnston <david.g.johnston@gmail.com>
@ 2024-07-19 04:05 ` Tom Lane <tgl@sss.pgh.pa.us>
2024-07-19 04:14 ` Re: A minor bug in the doc of "SQL Functions Returning Sets" in xfunc.sgml. David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Tom Lane @ 2024-07-19 04:05 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: Michael Paquier <michael@paquier.xyz>; 日向充 <mitsuru.hinata.5432@gmail.com>; pgsql-docs@lists.postgresql.org
"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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: A minor bug in the doc of "SQL Functions Returning Sets" in xfunc.sgml.
2024-07-19 01:46 A minor bug in the doc of "SQL Functions Returning Sets" in xfunc.sgml. 日向充 <mitsuru.hinata.5432@gmail.com>
2024-07-19 02:10 ` Re: A minor bug in the doc of "SQL Functions Returning Sets" in xfunc.sgml. Michael Paquier <michael@paquier.xyz>
2024-07-19 03:55 ` Re: A minor bug in the doc of "SQL Functions Returning Sets" in xfunc.sgml. David G. Johnston <david.g.johnston@gmail.com>
2024-07-19 04:05 ` Re: A minor bug in the doc of "SQL Functions Returning Sets" in xfunc.sgml. Tom Lane <tgl@sss.pgh.pa.us>
@ 2024-07-19 04:14 ` David G. Johnston <david.g.johnston@gmail.com>
2024-07-22 06:33 ` Re: A minor bug in the doc of "SQL Functions Returning Sets" in xfunc.sgml. 日向充 <mitsuru.hinata.5432@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: David G. Johnston @ 2024-07-19 04:14 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Michael Paquier <michael@paquier.xyz>; 日向充 <mitsuru.hinata.5432@gmail.com>; pgsql-docs@lists.postgresql.org
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.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: A minor bug in the doc of "SQL Functions Returning Sets" in xfunc.sgml.
2024-07-19 01:46 A minor bug in the doc of "SQL Functions Returning Sets" in xfunc.sgml. 日向充 <mitsuru.hinata.5432@gmail.com>
2024-07-19 02:10 ` Re: A minor bug in the doc of "SQL Functions Returning Sets" in xfunc.sgml. Michael Paquier <michael@paquier.xyz>
2024-07-19 03:55 ` Re: A minor bug in the doc of "SQL Functions Returning Sets" in xfunc.sgml. David G. Johnston <david.g.johnston@gmail.com>
2024-07-19 04:05 ` Re: A minor bug in the doc of "SQL Functions Returning Sets" in xfunc.sgml. Tom Lane <tgl@sss.pgh.pa.us>
2024-07-19 04:14 ` Re: A minor bug in the doc of "SQL Functions Returning Sets" in xfunc.sgml. David G. Johnston <david.g.johnston@gmail.com>
@ 2024-07-22 06:33 ` 日向充 <mitsuru.hinata.5432@gmail.com>
0 siblings, 0 replies; 6+ messages in thread
From: 日向充 @ 2024-07-22 06:33 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Michael Paquier <michael@paquier.xyz>; pgsql-docs@lists.postgresql.org
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.
>
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2024-07-22 06:33 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-07-19 01:46 A minor bug in the doc of "SQL Functions Returning Sets" in xfunc.sgml. 日向充 <mitsuru.hinata.5432@gmail.com>
2024-07-19 02:10 ` Michael Paquier <michael@paquier.xyz>
2024-07-19 03:55 ` David G. Johnston <david.g.johnston@gmail.com>
2024-07-19 04:05 ` Tom Lane <tgl@sss.pgh.pa.us>
2024-07-19 04:14 ` David G. Johnston <david.g.johnston@gmail.com>
2024-07-22 06:33 ` 日向充 <mitsuru.hinata.5432@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox