From: "Robert B. Easter" <reaster@comptechnews.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Subject: Re: docs: syntax.sgml patch
Date: Sun, 14 Jan 2001 19:13:09 -0500
Content-Type: text/plain;
  charset="iso-8859-1"
Cc: pgsql-patches@postgresql.org
References: <0101132352370A.15894@comptechnews> <3135.979505134@sss.pgh.pa.us>
In-Reply-To: <3135.979505134@sss.pgh.pa.us>
MIME-Version: 1.0
Message-Id: <01011419130900.02219@comptechnews>
Content-Transfer-Encoding: 8bit

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/ ------------