Thread: Re: Values list-of-targetlists patch for comments (was Re: [PATCHES]
Gavin Sherry <swm@linuxworld.com.au> writes:
> Docs and regression tests attached.
I've applied the regression tests (with a few additions), but I'm
feeling dissatisfied with this approach to documenting VALUES.
It seems to be mostly missing the point about VALUES being usable
whereever SELECT is. I'm not at all sure what I'd do instead though.
Should we give VALUES its own reference page? That doesn't quite
seem helpful either. cc'ing to pgsql-docs for ideas.
regards, tom lane
On Aug 3, 2006, at 23:58 , Tom Lane wrote: > Should we give VALUES its own reference page? That doesn't quite > seem helpful either. > I think we should go for a separate reference page, as VALUES appears to be expanding quite a bit. Up till now I've thought of VALUES only in conjunction with UPDATE, so perhaps a useful alternative would be to keep all of the information regarding VALUES and its syntax would be as a large part of the UPDATE reference page, though that would imply by placement (even if explained otherwise) that VALUES is only a part of the UPDATE syntax, which it no longer (?) is. That brings me back to the idea of VALUES deserving its own reference page. I wonder how soon pretty much the entire SQL spec will be duplicated in the PostgreSQL documentation. :) Michael Glaesemann grzm seespotcode net
Tom Lane wrote: > Should we give VALUES its own reference page? That doesn't quite > seem helpful either. cc'ing to pgsql-docs for ideas. This is probably the sort of thing that should be explained part II "The SQL Language". In particular, section 7.2 is about table expressions, which this is either a case of or related to. A few examples on the command references pages to show how it can be useful are probably OK, but the big picture shoulbd be developed in chapter 7 or so. -- Peter Eisentraut http://developer.postgresql.org/~petere/
Peter Eisentraut wrote: > Tom Lane wrote: > >>Should we give VALUES its own reference page? That doesn't quite >>seem helpful either. cc'ing to pgsql-docs for ideas. > > This is probably the sort of thing that should be explained part II "The > SQL Language". In particular, section 7.2 is about table expressions, > which this is either a case of or related to. > > A few examples on the command references pages to show how it can be > useful are probably OK, but the big picture shoulbd be developed in > chapter 7 or so. > I'll need some time (perhaps a few weeks because I'm just starting to do business travel again), but I'll take responsibility to write something up if you'd like. Joe
On Aug 4, 2006, at 9:42 , Gavin Sherry wrote: > ... with update? I associate it very closely with INSERT. After all, > INSERT is the only statement where we've had VALUES as part of the > grammar. Of course! Thanks for catching the glitch. I must have a bad RAM chip. Michael Glaesemann grzm seespotcode net
Gavin Sherry wrote: > What do we want to do about documenting: > > regression=# values(1); Out of curiosity, according to what theory should that be allowed? -- Peter Eisentraut http://developer.postgresql.org/~petere/
Peter Eisentraut <peter_e@gmx.net> writes:
> Gavin Sherry wrote:
>> What do we want to do about documenting:
>> regression=# values(1);
> Out of curiosity, according to what theory should that be allowed?
Wrong question. SELECT (for the general case of multi-row results)
and VALUES are exactly parallel in the SQL grammar; the right question
is "according to what theory are you allowed to issue a general SELECT?"
AFAICT the SQL spec only envisions "SELECT ... INTO some-variables",
restricted to a single-row result (cf <select statement: single row>
production) as being something a client can issue directly. Every more-
complex case is apparently supposed to be handled by one-row-at-a-time
fetches from a cursor.
If you can persuade the community that they don't want SELECT in its
current form, you might be able to persuade me that VALUES shouldn't
be allowed either.
regards, tom lane
Gavin Sherry <swm@linuxworld.com.au> writes:
> On Fri, 4 Aug 2006, Michael Glaesemann wrote:
>> On Aug 3, 2006, at 23:58 , Tom Lane wrote:
>>> Should we give VALUES its own reference page? That doesn't quite
>>> seem helpful either.
>>
>> I think we should go for a separate reference page, as VALUES appears
>> to be expanding quite a bit.
> ... with update? I associate it very closely with INSERT. After all,
> INSERT is the only statement where we've had VALUES as part of the
> grammar.
True, but I think that's just a historical artifact. If you look at the
SQL spec, INSERT ... VALUES and INSERT ... SELECT are not distinct
constructs: they fall out of the fact that VALUES and SELECT are allowed
interchangeably.
<insert statement> ::=
INSERT INTO <table name>
<insert columns and source>
<insert columns and source> ::=
[ <left paren> <insert column list> <right paren> ]
<query expression>
| DEFAULT VALUES
<insert column list> ::= <column name list>
and when you trace down <query expression> you find the SELECT
and VALUES options entering at exactly the same place ...
I'd like to see us refactor the docs as necessary to reflect that idea.
Peter is right that this needs some discussion in syntax.sgml as well as
in the reference pages --- but I'm still not very clear on how the
presentation should go.
regards, tom lane
Tom Lane wrote: > Wrong question. SELECT (for the general case of multi-row results) > and VALUES are exactly parallel in the SQL grammar; the right > question is "according to what theory are you allowed to issue a > general SELECT?" The "top-level" SELECT for interactive use is <direct SQL statement> ::= <directly executable statement> <semicolon> <directly executable statement> ::= <direct SQL data statement> | ... <direct SQL data statement> ::= <direct select statement: multiple rows> | ... But this actually does resolve as just VALUES (something), so nevermind. -- Peter Eisentraut http://developer.postgresql.org/~petere/
On Thu, 2006-08-03 at 10:58 -0400, Tom Lane wrote: > Gavin Sherry <swm@linuxworld.com.au> writes: > > Docs and regression tests attached. > > I've applied the regression tests (with a few additions), but I'm > feeling dissatisfied with this approach to documenting VALUES. > It seems to be mostly missing the point about VALUES being usable > whereever SELECT is. I'm not at all sure what I'd do instead though. > Should we give VALUES its own reference page? That doesn't quite > seem helpful either. cc'ing to pgsql-docs for ideas. The DB2 manual does exactly that and is not any clearer as a result, even if it is fully normalised. If we did that we'd need to emphasise that VALUES is more of a clause than a new command. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com
Re: [HACKERS] [PATCHES] Values list-of-targetlists patch for comments (was Re:
From
Peter Eisentraut
Date:
Am Freitag, 4. August 2006 04:50 schrieb Tom Lane: > I'd like to see us refactor the docs as necessary to reflect that idea. > Peter is right that this needs some discussion in syntax.sgml as well as > in the reference pages --- but I'm still not very clear on how the > presentation should go. I'm beginning to think that VALUES might be a separate command after all. -- Peter Eisentraut http://developer.postgresql.org/~petere/
Re: [HACKERS] [PATCHES] Values list-of-targetlists patch for comments (was Re:
From
David Fetter
Date:
On Wed, Aug 09, 2006 at 03:05:02PM +0200, Peter Eisentraut wrote:
> Am Freitag, 4. August 2006 04:50 schrieb Tom Lane:
> > I'd like to see us refactor the docs as necessary to reflect that
> > idea. Peter is right that this needs some discussion in
> > syntax.sgml as well as in the reference pages --- but I'm still
> > not very clear on how the presentation should go.
>
> I'm beginning to think that VALUES might be a separate command after
> all.
What's below definitely bolsters that idea :)
postgres=# VALUES(1);
column1
---------
1
(1 row)
However, there are some oddities:
postgres=# SELECT * FROM (VALUES (1,2)) AS foo(bar,baz);
bar | baz
-----+-----
1 | 2
(1 row)
postgres=# (VALUES (1,2)) AS foo(bar,baz);
ERROR: syntax error at or near "AS"
LINE 1: (VALUES (1,2)) AS foo(bar,baz);
Does the SQL standard have anything to say about assigning identifiers
both to the entire VALUES() statement and to its columns when the
VALUES() statement is by itself?
Cheers,
D
--
David Fetter <david@fetter.org> http://fetter.org/
phone: +1 415 235 3778 AIM: dfetter666
Skype: davidfetter
Remember to vote!
David Fetter <david@fetter.org> writes:
> However, there are some oddities:
> postgres=# SELECT * FROM (VALUES (1,2)) AS foo(bar,baz);
> [ works ]
> postgres=# (VALUES (1,2)) AS foo(bar,baz);
> ERROR: syntax error at or near "AS"
This is per spec. Effectively, AS is part of the FROM-clause syntax
not part of a standalone command. You can't write this either:
regression=# (select 1,2) as foo(bar,baz);
ERROR: syntax error at or near "as"
LINE 1: (select 1,2) as foo(bar,baz);
^
regards, tom lane
On Thu, 3 Aug 2006, Joe Conway wrote: > Peter Eisentraut wrote: > > Tom Lane wrote: > > > >>Should we give VALUES its own reference page? That doesn't quite > >>seem helpful either. cc'ing to pgsql-docs for ideas. > > > > This is probably the sort of thing that should be explained part II "The > > SQL Language". In particular, section 7.2 is about table expressions, > > which this is either a case of or related to. > > > > A few examples on the command references pages to show how it can be > > useful are probably OK, but the big picture shoulbd be developed in > > chapter 7 or so. > > > > I'll need some time (perhaps a few weeks because I'm just starting to do > business travel again), but I'll take responsibility to write something > up if you'd like. I'm happy to finish what I started. Gavin
On Thu, 3 Aug 2006, Tom Lane wrote: > Gavin Sherry <swm@linuxworld.com.au> writes: > > Docs and regression tests attached. > > I've applied the regression tests (with a few additions), but I'm > feeling dissatisfied with this approach to documenting VALUES. > It seems to be mostly missing the point about VALUES being usable > whereever SELECT is. I'm not at all sure what I'd do instead though. > Should we give VALUES its own reference page? That doesn't quite > seem helpful either. cc'ing to pgsql-docs for ideas. Good point. One question: are we happy calling this a 'VALUES list'? It's better than a 'table value constructor'. I took the lead from a comment in the source. Thanks, gavin
On Fri, 4 Aug 2006, Michael Glaesemann wrote: > On Aug 3, 2006, at 23:58 , Tom Lane wrote: > > > > Should we give VALUES its own reference page? That doesn't quite > > seem helpful either. > > > > I think we should go for a separate reference page, as VALUES appears > to be expanding quite a bit. Up till now I've thought of VALUES only > in conjunction with UPDATE, so perhaps a useful alternative would be > to keep all of the information regarding VALUES and its syntax would > be as a large part of the UPDATE reference page, though that would > imply by placement (even if explained otherwise) that VALUES is only > a part of the UPDATE syntax, which it no longer (?) is. That brings > me back to the idea of VALUES deserving its own reference page. ... with update? I associate it very closely with INSERT. After all, INSERT is the only statement where we've had VALUES as part of the grammar. Thanks, Gavin
On Thu, 3 Aug 2006, Peter Eisentraut wrote:
> Tom Lane wrote:
> > Should we give VALUES its own reference page? That doesn't quite
> > seem helpful either. cc'ing to pgsql-docs for ideas.
>
> This is probably the sort of thing that should be explained part II "The
> SQL Language". In particular, section 7.2 is about table expressions,
> which this is either a case of or related to.
Yes, good idea.
>
> A few examples on the command references pages to show how it can be
> useful are probably OK, but the big picture shoulbd be developed in
> chapter 7 or so.
What do we want to do about documenting:
regression=# values(1);
column1
---------
1
(1 row)
It seems to me that if this isn't in the command reference we'll see an
email every few months say 'is this an undocumented feature?'. We can
cover it easily in Part II, 7.2 but people wont look there. Then again, it
just doesn't seem right to put it in the command reference as a 'top
level' command.
Gavin
On Fri, 4 Aug 2006, Peter Eisentraut wrote:
> Gavin Sherry wrote:
> > What do we want to do about documenting:
> >
> > regression=# values(1);
>
> Out of curiosity, according to what theory should that be allowed?
I asked this too. Tom pointed this out (I'd give the URL if I could find
it on archives):
--
You bet. VALUES is parallel to SELECT in the SQL grammar, so AFAICS
it should be legal anywhere you can write SELECT.
The basic productions in the spec's grammar are respectively
<query specification> ::=
SELECT [ <set quantifier> ] <select list>
<table expression>
and
<table value constructor> ::=
VALUES <row value expression list>
--
which seems to be the case from my reading of the spec.
Gavin
> >Good point. One question: are we happy calling this a 'VALUES list'? It's >better than a 'table value constructor'. I took the lead from a comment in >the >source. > table value constructor is name from ANSI. All people wiil find t.v.c., not values list. I vote table value constructor. Regards Pavel Stehule _________________________________________________________________ Emotikony a pozadi programu MSN Messenger ozivi vasi konverzaci. http://messenger.msn.cz/
Gavin Sherry wrote: > > A few examples on the command references pages to show how it can be > > useful are probably OK, but the big picture shoulbd be developed in > > chapter 7 or so. > > What do we want to do about documenting: > > regression=# values(1); > column1 > --------- > 1 > (1 row) > > It seems to me that if this isn't in the command reference we'll see an > email every few months say 'is this an undocumented feature?'. We can > cover it easily in Part II, 7.2 but people wont look there. Then again, it > just doesn't seem right to put it in the command reference as a 'top > level' command. How many people are going to think of doing a VALUES(1)? Isn't it just like SELECT 1? We can try making VALUES a non-toplevel command, and if people get confused, we can promote in the documenation. I think starting it at top-level is perhaps confusing. -- Bruce Momjian bruce@momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
On Aug 11, 2006, at 12:07 , Bruce Momjian wrote: > Gavin Sherry wrote: >>> A few examples on the command references pages to show how it can be >>> useful are probably OK, but the big picture shoulbd be developed in >>> chapter 7 or so. >> >> What do we want to do about documenting: >> >> regression=# values(1); >> column1 >> --------- >> 1 >> (1 row) >> >> It seems to me that if this isn't in the command reference we'll >> see an >> email every few months say 'is this an undocumented feature?'. We can >> cover it easily in Part II, 7.2 but people wont look there. Then >> again, it >> just doesn't seem right to put it in the command reference as a 'top >> level' command. > > How many people are going to think of doing a VALUES(1)? Isn't it > just > like SELECT 1? We can try making VALUES a non-toplevel command, > and if > people get confused, we can promote in the documenation. I think > starting it at top-level is perhaps confusing. Perhaps it may be confusing, but at the same time, it's educational to put VALUES at the top level. Having not read the SQL spec in detail, I do rely on the Postgres documentation as a quick reference. Thanks to the high-level of SQL-spec compliance, the Postgres documentation is pretty helpful in that way. And I'm always pleasantly surprised when I learn something new just from looking indices such as this. Michael Glaesemann grzm seespotcode net