Thread: docs: syntax.sgml patch
I've made an attempt to better describe the FROM clause and table expressions in general for the SQL Syntax: Expressions page in the User's Guide. My patch replaces the "FROM clause" section that was in there with a section called "Table Expressions". It includes a description of the FROM clause and also the WHERE, GROUP BY, HAVING, ORDER BY, and LIMIT clauses. The JOIN syntax is covered. Subqueries are also explained some. There could be some mistakes or statements that imply a limitation that is not true (maybe). I encourage people to review it and correct any mistakes or clean up the language etc. If it's not right for being applied, let me know. I'm hoping this material will fit in and be developed further with more examples later. -- -------- Robert B. Easter reaster@comptechnews.com --------- -- CompTechNews Message Board http://www.comptechnews.com/ -- -- CompTechServ Tech Services http://www.comptechserv.com/ -- ---------- http://www.comptechnews.com/~reaster/ ------------
Attachment
"Robert B. Easter" <reaster@comptechnews.com> writes:
> I've made an attempt to better describe the FROM clause and table expressions
> in general for the SQL Syntax: Expressions page in the User's Guide.
A good start. Here are some suggestions for improving it.
1. I think this material should be moved out of the "Expressions"
section and made into its own level-1 section. In the first place,
it's now larger than the rest of the section put together, and in the
second place table expressions are not the same kind of expression that
the rest of the section is talking about. (Perhaps the "Expressions"
section should be retitled "Arithmetic Expressions" or some such.)
The mention of scalar subqueries does belong under arithmetic
expressions, but ISTM the rest of this doesn't.
Another possibility is to make a new level-1 section that talks about
the syntax of the SELECT statement in general, with subsections along
the lines of
Target list
[take this part out of Expressions]
Source table (FROM/WHERE/GROUP BY/HAVING)
[most of what you wrote]
Display constraints (ORDER BY/LIMIT)
[last part of what you wrote]
UNION/INTERSECT/EXCEPT
[to be written]
2. I don't much like the way you use the term "input table"; that seems
to me to connote a real table that is being used as one of the initial
inputs to a SELECT. Perhaps use something like "final derived table" to
refer to the output of the table expression, with "output table" being
the result of the SELECT itself.
3. The references to scope seem to just be confusing here. I'd leave
them out of this discussion and plan to develop a separate section
discussing name scope and outer references.
4. Your practice of showing several examples in a group and then
explaining them seems confusing, because it's not always clear which
example any particular sentence is referring to. I'd say keep to a
strict one-example, one-commentary alternation.
5. In some places you have {curly braces} around syntactic variables.
Don't do that, use the appropriate markup.
6. Both SQL92 and Postgres allow a HAVING clause without GROUP BY.
It's kinda pointless, perhaps, but it's allowed.
7. It's not correct to say that standard SQL only allows ORDER BY in
cursors; there's also
<direct select statement: multiple rows> ::=
<query expression> [ <order by clause> ]
regards, tom lane
On Sunday 14 January 2001 15:45, Tom Lane wrote:
> "Robert B. Easter" <reaster@comptechnews.com> writes:
> > I've made an attempt to better describe the FROM clause and table
> > expressions in general for the SQL Syntax: Expressions page in the User's
> > Guide.
>
>
> 1. I think this material should be moved out of the "Expressions"
> section and made into its own level-1 section. In the first place,
> it's now larger than the rest of the section put together, and in the
> second place table expressions are not the same kind of expression that
> the rest of the section is talking about. (Perhaps the "Expressions"
> section should be retitled "Arithmetic Expressions" or some such.)
> The mention of scalar subqueries does belong under arithmetic
> expressions, but ISTM the rest of this doesn't.
Thanks for feedback!
I see what you are saying and I expected my stuff to not fit so well. The
FROM list section, which I replaced, doesn't really belong there either. The
whole outline of the SQL Syntax chapter could maybe use a redesign with a
structure that follows the breakdown of SQL as expressed in the SQL standards
documents and then Postgres extensions/compatibility chapters or material
mixed in.
The basic outline of the standard I read (SQL1999) is like:
1. Scope
2. Normative References
3. Definitions, notations, conventions.
4. Concepts
5. Lexical Elements
6. Scalar Expressions
Value Expression
Scalar Subquery
Case Expression
Element Reference
7. Query Expressions
Table Expression
FROM clause
WHERE clause
etc ...
8. Predicates
LIKE
BETWEEN
EXISTS
9. Data Assignment Rules and Routine Determination
10. Additional Common Elements
11. Schema Definition and Manipulation
12. Access Control
13. SQL-client modules
14. Data Manipulation
15. Control Statements
16. Transaction Management
17. Connection Management
18. Session Management
19. Diagnostics Management
20. Information Schema
21. Definition Schema
22. Status Codes
Anyhow, this outline could be a basis from which to build an "SQL Guide" or
something. Maybe calling it SQL Syntax is not the best. More than just pure
syntax is laid out. The semantics is explained too. The standard document
has a good structure but is very formal. Taking this structure and writing
something that is normal, readable english with real examples from PostgreSQL
would be something good but is a large task - basically, derive a document
based on the standard doc (no verbatim plagiarism though). Maybe not all of
the chapters above make sense for PostgreSQL now if it's not all implemented,
but that outline could be reserved, in a way, for future development.
The current "Expressions" section, as it is, might better be called "Scalar
Expressions" and then move the "Table Expressions" that I wrote under "Query
Expressions." etc ...
The reference section, from the man-pages etc, would still be good as like
quick reference cards.
Comments, anyone?
>
> Another possibility is to make a new level-1 section that talks about
> the syntax of the SELECT statement in general, with subsections along
> the lines of
> Target list
> [take this part out of Expressions]
> Source table (FROM/WHERE/GROUP BY/HAVING)
> [most of what you wrote]
> Display constraints (ORDER BY/LIMIT)
> [last part of what you wrote]
> UNION/INTERSECT/EXCEPT
> [to be written]
>
> 2. I don't much like the way you use the term "input table"; that seems
> to me to connote a real table that is being used as one of the initial
> inputs to a SELECT. Perhaps use something like "final derived table" to
> refer to the output of the table expression, with "output table" being
> the result of the SELECT itself.
I agree. I want to use standard terminology and take the time to introduce
and define terms clearly before using them. I was afraid to use "derived
table" all over the place because I thought it was too vague sounding. The
result of the whole query is also a derived table so things are confusing
maybe. Also, I saw the "input table" term used in some other PostgreSQL
docs, so I thought it might be consistent. It can be worked on more and the
terms improved.
>
> 3. The references to scope seem to just be confusing here. I'd leave
> them out of this discussion and plan to develop a separate section
> discussing name scope and outer references.
Ok. I'm not sure where that fits right now either.
>
> 4. Your practice of showing several examples in a group and then
> explaining them seems confusing, because it's not always clear which
> example any particular sentence is referring to. I'd say keep to a
> strict one-example, one-commentary alternation.
>
I know, its confusing. :) Examples need work.
> 5. In some places you have {curly braces} around syntactic variables.
> Don't do that, use the appropriate markup.
It would be more like:
SELECT <select list> <table expression>
?? DocBook puts those curly braces on automatically when I use choice="req"
in a <arg> tag. Guess I can't use those DocBook tags all the time unless it
can do < and > with some other tag or option.
>
> 6. Both SQL92 and Postgres allow a HAVING clause without GROUP BY.
> It's kinda pointless, perhaps, but it's allowed.
>
You're right, I've done it and it works like a WHERE clause. In SQL99, the
default in a HAVING clause, if group by is not specified, is GROUP BY (). A
GROUP BY () is called a <grand total>. I'll admit, the reading of the <group
by clause> is one of the more confusing ones. Whatever GROUP BY () is
supposed to do, PostgreSQL won't take it.
> 7. It's not correct to say that standard SQL only allows ORDER BY in
> cursors; there's also
>
> <direct select statement: multiple rows> ::=
> <query expression> [ <order by clause> ]
>
> regards, tom lane
Again, I'm reading SQL1999 so things are different. I managed to see a copy
of SQL92 and found what you quote. However, direct select statement is not
present in the SQL1999 file I've been reading. In the file I read, <order by
clause> is only used in a <declare cursor> definition. I guess they intend
that if you really want ordered results, you have to create a cursor for it
now. But it doesn't matter too much since PostgreSQL is working on SQL92
compatibility, right.
The documentation seems to need a lot of work. I'm not sure how radically it
can be changed since I don't want to disturb others work any. I've just been
trying to squeeze in my stuff where I can fit it! :)
--
-------- Robert B. Easter reaster@comptechnews.com ---------
-- CompTechNews Message Board http://www.comptechnews.com/ --
-- CompTechServ Tech Services http://www.comptechserv.com/ --
---------- http://www.comptechnews.com/~reaster/ ------------
I know this SQL Guide has been discussed, but what should be done with this patch? [ Charset ISO-8859-1 unsupported, converting... ] > I've made an attempt to better describe the FROM clause and table expressions > in general for the SQL Syntax: Expressions page in the User's Guide. > > My patch replaces the "FROM clause" section that was in there with a section > called "Table Expressions". It includes a description of the FROM clause and > also the WHERE, GROUP BY, HAVING, ORDER BY, and LIMIT clauses. The JOIN > syntax is covered. Subqueries are also explained some. > > There could be some mistakes or statements that imply a limitation that is > not true (maybe). I encourage people to review it and correct any mistakes > or clean up the language etc. > > If it's not right for being applied, let me know. I'm hoping this material > will fit in and be developed further with more examples later. > > -- > -------- Robert B. Easter reaster@comptechnews.com --------- > -- CompTechNews Message Board http://www.comptechnews.com/ -- > -- CompTechServ Tech Services http://www.comptechserv.com/ -- > ---------- http://www.comptechnews.com/~reaster/ ------------ [ Attachment, skipping... ] -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
Bruce Momjian writes: > I know this SQL Guide has been discussed, but what should be done with > this patch? I believe he's going to revise it to address Tom Lane's suggestions. -- Peter Eisentraut peter_e@gmx.net http://yi.org/peter-e/
I worked on the ORDER BY paragraphs some more. See what you think of it now. The patch is attached. :) On Thursday 18 January 2001 18:48, Peter Eisentraut wrote: > Bruce Momjian writes: > > I know this SQL Guide has been discussed, but what should be done with > > this patch? > > I believe he's going to revise it to address Tom Lane's suggestions. -- -------- Robert B. Easter reaster@comptechnews.com --------- -- CompTechNews Message Board http://www.comptechnews.com/ -- -- CompTechServ Tech Services http://www.comptechserv.com/ -- ---------- http://www.comptechnews.com/~reaster/ ------------
Attachment
I've checked this in, or at least I used it as the starting point for what I did. The GROUP BY and ORDER BY explanations seemed really complicated, so I disabled them for now. They need to be made simpler, and an example would be nice. Essentially, this section is now the description of the SELECT command; maybe it should be named like that, too. Robert B. Easter writes: > I worked on the ORDER BY paragraphs some more. See what you think of it now. > The patch is attached. :) > > > On Thursday 18 January 2001 18:48, Peter Eisentraut wrote: > > Bruce Momjian writes: > > > I know this SQL Guide has been discussed, but what should be done with > > > this patch? > > > > I believe he's going to revise it to address Tom Lane's suggestions. > > -- Peter Eisentraut peter_e@gmx.net http://yi.org/peter-e/
Thanks for getting some of the table expressions into the docs. I think it will be helpful to people. I'll see what I can do with the GROUP BY and HAVING clauses, unless you beat me to it! Thanks again. On Sunday 21 January 2001 17:14, Peter Eisentraut wrote: > I've checked this in, or at least I used it as the starting point for what > I did. The GROUP BY and ORDER BY explanations seemed really complicated, > so I disabled them for now. They need to be made simpler, and an example > would be nice. Essentially, this section is now the description of the > SELECT command; maybe it should be named like that, too. > > Robert B. Easter writes: > > I worked on the ORDER BY paragraphs some more. See what you think of it > > now. The patch is attached. :) > > > > On Thursday 18 January 2001 18:48, Peter Eisentraut wrote: > > > Bruce Momjian writes: > > > > I know this SQL Guide has been discussed, but what should be done > > > > with this patch? > > > > > > I believe he's going to revise it to address Tom Lane's suggestions. -- -------- Robert B. Easter reaster@comptechnews.com --------- -- CompTechNews Message Board http://www.comptechnews.com/ -- -- CompTechServ Tech Services http://www.comptechserv.com/ -- ---------- http://www.comptechnews.com/~reaster/ ------------
I saw the commented sections about GROUP BY etc. I've taken another try at GROUP BY in the patch (attached). I moved the commenting to cover ORDER BY and LIMIT now. On Sunday 21 January 2001 17:14, Peter Eisentraut wrote: > I've checked this in, or at least I used it as the starting point for what > I did. The GROUP BY and ORDER BY explanations seemed really complicated, > so I disabled them for now. They need to be made simpler, and an example > would be nice. Essentially, this section is now the description of the > SELECT command; maybe it should be named like that, too. > > Robert B. Easter writes: > > I worked on the ORDER BY paragraphs some more. See what you think of it > > now. The patch is attached. :) > > > > On Thursday 18 January 2001 18:48, Peter Eisentraut wrote: > > > Bruce Momjian writes: > > > > I know this SQL Guide has been discussed, but what should be done > > > > with this patch? > > > > > > I believe he's going to revise it to address Tom Lane's suggestions. -- -------- Robert B. Easter reaster@comptechnews.com --------- -- CompTechNews Message Board http://www.comptechnews.com/ -- -- CompTechServ Tech Services http://www.comptechserv.com/ -- ---------- http://www.comptechnews.com/~reaster/ ------------
Attachment
Woops. syntax.sgml.patch.5 is attached. Don't use syntax.sgml.patch.4, it has a mistake in it. Sorry. (Hopefully no other errors!) On Sunday 21 January 2001 17:14, Peter Eisentraut wrote: > I've checked this in, or at least I used it as the starting point for what > I did. The GROUP BY and ORDER BY explanations seemed really complicated, > so I disabled them for now. They need to be made simpler, and an example > would be nice. Essentially, this section is now the description of the > SELECT command; maybe it should be named like that, too. > > Robert B. Easter writes: > > I worked on the ORDER BY paragraphs some more. See what you think of it > > now. The patch is attached. :) > > > > On Thursday 18 January 2001 18:48, Peter Eisentraut wrote: > > > Bruce Momjian writes: > > > > I know this SQL Guide has been discussed, but what should be done > > > > with this patch? > > > > > > I believe he's going to revise it to address Tom Lane's suggestions. -- -------- Robert B. Easter reaster@comptechnews.com --------- -- CompTechNews Message Board http://www.comptechnews.com/ -- -- CompTechServ Tech Services http://www.comptechserv.com/ -- ---------- http://www.comptechnews.com/~reaster/ ------------
Attachment
Robert, this in all in CVS, right? > Woops. syntax.sgml.patch.5 is attached. Don't use syntax.sgml.patch.4, it > has a mistake in it. Sorry. (Hopefully no other errors!) > > > > On Sunday 21 January 2001 17:14, Peter Eisentraut wrote: > > I've checked this in, or at least I used it as the starting point for what > > I did. The GROUP BY and ORDER BY explanations seemed really complicated, > > so I disabled them for now. They need to be made simpler, and an example > > would be nice. Essentially, this section is now the description of the > > SELECT command; maybe it should be named like that, too. > > > > Robert B. Easter writes: > > > I worked on the ORDER BY paragraphs some more. See what you think of it > > > now. The patch is attached. :) > > > > > > On Thursday 18 January 2001 18:48, Peter Eisentraut wrote: > > > > Bruce Momjian writes: > > > > > I know this SQL Guide has been discussed, but what should be done > > > > > with this patch? > > > > > > > > I believe he's going to revise it to address Tom Lane's suggestions. > > -- > -------- Robert B. Easter reaster@comptechnews.com --------- > -- CompTechNews Message Board http://www.comptechnews.com/ -- > -- CompTechServ Tech Services http://www.comptechserv.com/ -- > ---------- http://www.comptechnews.com/~reaster/ ------------ [ Attachment, skipping... ] -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026