Thread: TABLE command

TABLE command

From
Peter Eisentraut
Date:
If I read this right, SQL would allow writing

TABLE foo;

as a top-level command, equivalent to SELECT * FROM foo; (see production
<explicit table>).  It can be used whereever a SELECT or VALUES can be used.

This is probably about as useless as some of my other recent patches,
but the implementation is simple (see attachment), so we could add it.
Comments?
diff -ur ../cvs-pgsql/src/backend/parser/gram.y ./src/backend/parser/gram.y
--- ../cvs-pgsql/src/backend/parser/gram.y    2008-10-29 13:37:47.000000000 +0200
+++ ./src/backend/parser/gram.y    2008-10-29 16:49:09.000000000 +0200
@@ -6416,6 +6416,25 @@
                     $$ = (Node *)n;
                 }
             | values_clause                            { $$ = $1; }
+            | TABLE table_ref
+                {
+                    /* same as SELECT * FROM table_ref */
+                    ColumnRef *cr = makeNode(ColumnRef);
+                    ResTarget *rt = makeNode(ResTarget);
+                    SelectStmt *n = makeNode(SelectStmt);
+
+                    cr->fields = list_make1(makeNode(A_Star));
+                    cr->location = -1;
+
+                    rt->name = NULL;
+                    rt->indirection = NIL;
+                    rt->val = (Node *)cr;
+                    rt->location = -1;
+
+                    n->targetList = list_make1(rt);
+                    n->fromClause = list_make1($2);
+                    $$ = (Node *)n;
+                }
             | select_clause UNION opt_all select_clause
                 {
                     $$ = makeSetOp(SETOP_UNION, $3, $1, $4);

Re: TABLE command

From
Tom Lane
Date:
Peter Eisentraut <peter_e@gmx.net> writes:
> If I read this right, SQL would allow writing
> TABLE foo;

> as a top-level command, equivalent to SELECT * FROM foo; (see production 
> <explicit table>).  It can be used whereever a SELECT or VALUES can be used.

> This is probably about as useless as some of my other recent patches, 
> but the implementation is simple (see attachment), so we could add it. 
> Comments?

Considering how ugly it was to fit top-level VALUES into our
documentation, I'm not really looking forward to this one.  I'd vote no,
at least until such time as we see some field demand for it.
        regards, tom lane


Re: TABLE command

From
"David Rowley"
Date:
Peter Eisentraut Wrote:
> If I read this right, SQL would allow writing
>
> TABLE foo;

Interesting; I managed to find it in the spec:

<Quote>
4) The <explicit table>
TABLE <table or query name>
is equivalent to the <table subquery>
( SELECT * FROM <table or query name> )
</Quote>

So going by that would the patch also have to support something like:

WITH a AS (SELECT * FROM b)
TABLE a; ?

I'd probably find it hard to find a use case. I'm too used to using SELECT *
FROM .. in psql. On the other hand last night I read a good web page
comparing the most popular RDBMS' for spec. compliance and PostgreSQL
probably was the most compliant all of the ones listed, (at least on the
topics covered). Oracle fails badly on '' IS NULL being true.

I enjoy seeing more spec compliant things being added. But on the other
hand, going with Tom's comments, if its lots of work for little gain...





Re: TABLE command

From
Tom Lane
Date:
Peter Eisentraut <peter_e@gmx.net> writes:
> +            | TABLE table_ref

BTW, this seems to accept *way* more than is intended by the spec.
I think a closer approximation would be "TABLE relation_expr".
You could even make a case for "TABLE qualified_name", which is
what the letter of the spec seems to demand; but it's probably
reasonable to allow ONLY decoration.
        regards, tom lane


Re: TABLE command

From
"Robert Haas"
Date:
Hi,

I was assigned to code-review this patch by pgsql-rrreviewers.  I
don't have much to add to what's already been written, but here are my
thoughts.

1. I agree with Tom Lane's earlier comments that table_ref is not the
correct non-terminal. For example, this seems pretty strange:

rhaas=# table position('i' in 'team');position
----------       0

As far as I can tell from looking around a bit (I don't actually have
a copy of the SQL:2008 spec), the intention is to allow only base
tables or views or references to WITH tables that are in scope.  I'm
not sure there's any good reason for that, but with TABLE as the key
word it's just too weird to allow random functions, function-like
operators, etc.

2. Realizing that this patch may have only been intended as a
proof-of-concept, it's pretty incomplete.  In addition to updating the
SGML documentation, it needs to update the psql documentation and tab
completion code, and maybe other, similar things that I'm not thinking
of.

rhaas=# \h table
No help available for "table".
Try \h with no arguments to see available help.

Incidentally, I noticed while looking at this that "\h with" also
fails, even though WITH can now be the first word of a valid SQL
statement.  I think we ought to patch psql to return the same help for
WITH as it does for SELECT.

3. Although I don't feel strongly about it, I tend to disagree with
the notion that "we don't need this".  It's not the most useful
feature in the world, but it's in the spec, and there are situations
where it may save a bit of typing.  Since SQL tends to be somewhat
wordy, I think this is a good thing.  YMMV.

I will update the status of this patch on the Wiki to "Waiting on author".

...Robert


Re: TABLE command

From
Tom Lane
Date:
"Robert Haas" <robertmhaas@gmail.com> writes:
> Incidentally, I noticed while looking at this that "\h with" also
> fails, even though WITH can now be the first word of a valid SQL
> statement.  I think we ought to patch psql to return the same help for
> WITH as it does for SELECT.

Hmm.  Given the current infrastructure for \h, the only way to do that
would be to make a separate ref page for WITH, which feels like the
wrong thing.  And the objection I have to TABLE is not the code but the
apparent need to give it its own ref page (as we already did for VALUES,
and I found that pretty ugly too).

Is there a way to make all of these point at the SELECT ref page?
Something cleaner than a special hack in psql would be nice, but
I guess I'd settle for that as still an improvement over considering
that TABLE is a command.  The problem with documenting VALUES and
TABLE as commands is that this doesn't reflect their principal use
as elements of a SELECT; and it also becomes quite unclear why you can't
use, say, EXPLAIN or SHOW as elements of a SELECT.
        regards, tom lane


Re: TABLE command

From
"Robert Haas"
Date:
> Hmm.  Given the current infrastructure for \h, the only way to do that
> would be to make a separate ref page for WITH, which feels like the
> wrong thing.  And the objection I have to TABLE is not the code but the
> apparent need to give it its own ref page (as we already did for VALUES,
> and I found that pretty ugly too).

Suck it up.  :-)  Presumably there could be more things in this
category in the future, so we'd better figure out how to handle it.

I just noticed that, at present, "\h WI" tab-completes to "\h WITH"
and then to "\h WITH RECURSIVE", but hitting return then tells you
that no help is actually available, which is pretty horrible.

> Is there a way to make all of these point at the SELECT ref page?

I think we could probably modify helpSQL() to support a list of
aliases.  I'm not sure how tricky that would be - the existing logic
appears slightly byzantine.  I'll take a crack at it if you would
like.

> Something cleaner than a special hack in psql would be nice, but
> I guess I'd settle for that as still an improvement over considering
> that TABLE is a command.  The problem with documenting VALUES and
> TABLE as commands is that this doesn't reflect their principal use
> as elements of a SELECT; and it also becomes quite unclear why you can't
> use, say, EXPLAIN or SHOW as elements of a SELECT.

Well, I suppose we could make it so that they can be.  *ducks*

...Robert


Re: TABLE command

From
Peter Eisentraut
Date:
Tom Lane wrote:
> "Robert Haas" <robertmhaas@gmail.com> writes:
>> Incidentally, I noticed while looking at this that "\h with" also
>> fails, even though WITH can now be the first word of a valid SQL
>> statement.  I think we ought to patch psql to return the same help for
>> WITH as it does for SELECT.
> 
> Hmm.  Given the current infrastructure for \h, the only way to do that
> would be to make a separate ref page for WITH, which feels like the
> wrong thing.

There is a canonical solution for this with man pages, namely man page 
links (those ".so" things in a man page that redirect to a different 
one).  In DocBook, you just list more than one refname in the refentry 
to create this.  I have committed a few bits of makefile to support 
this.  A bit more Perl hacking should also get psql up to speed.  I 
suggest we try if we like the results when WITH is linked to SELECT, and 
then see about TABLE and whatever else.


Re: TABLE command

From
Peter Eisentraut
Date:
Tom Lane wrote:
> Hmm.  Given the current infrastructure for \h, the only way to do that
> would be to make a separate ref page for WITH, which feels like the
> wrong thing.  And the objection I have to TABLE is not the code but the
> apparent need to give it its own ref page (as we already did for VALUES,
> and I found that pretty ugly too).
> 
> Is there a way to make all of these point at the SELECT ref page?

I was looking into that and I figured using the \G expression in Perl 
REs would be useful for this (to find multiple <refname>s).  But this 
was introduced in Perl 5.000, and we claim to support Perl 4 in 
create_help.pl.  I recall that you Tom once arranged this to support 
building on some old machines.  Is this still relevant?

Or can someone advise on an alternative way to code this?


Re: TABLE command

From
David Fetter
Date:
On Tue, Nov 18, 2008 at 04:01:58PM +0200, Peter Eisentraut wrote:
> Tom Lane wrote:
>> Hmm.  Given the current infrastructure for \h, the only way to do
>> that would be to make a separate ref page for WITH, which feels
>> like the wrong thing.  And the objection I have to TABLE is not the
>> code but the apparent need to give it its own ref page (as we
>> already did for VALUES, and I found that pretty ugly too).
>>
>> Is there a way to make all of these point at the SELECT ref page?
>
> I was looking into that and I figured using the \G expression in
> Perl  REs would be useful for this (to find multiple <refname>s).
> But this  was introduced in Perl 5.000, and we claim to support Perl
> 4 in  create_help.pl.  I recall that you Tom once arranged this to
> support  building on some old machines.  Is this still relevant?

No.  The last release of Perl 4 was in 1993.  People born since then
are probably hacking PostgreSQL right now.

> Or can someone advise on an alternative way to code this?

Leave Perl 4 support out. :)

Cheers,
David.
-- 
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david.fetter@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate


Re: TABLE command

From
Tom Lane
Date:
David Fetter <david@fetter.org> writes:
> On Tue, Nov 18, 2008 at 04:01:58PM +0200, Peter Eisentraut wrote:
>> I was looking into that and I figured using the \G expression in
>> Perl  REs would be useful for this (to find multiple <refname>s).
>> But this  was introduced in Perl 5.000, and we claim to support Perl
>> 4 in  create_help.pl.  I recall that you Tom once arranged this to
>> support  building on some old machines.  Is this still relevant?

> No.

I concur, there isn't likely to be much demand for building PG >= 8.4
with Perl 4.
        regards, tom lane


Re: TABLE command

From
"Joshua D. Drake"
Date:
On Tue, 2008-11-18 at 10:36 -0500, Tom Lane wrote:
> David Fetter <david@fetter.org> writes:
> > On Tue, Nov 18, 2008 at 04:01:58PM +0200, Peter Eisentraut wrote:
> >> I was looking into that and I figured using the \G expression in
> >> Perl  REs would be useful for this (to find multiple <refname>s).
> >> But this  was introduced in Perl 5.000, and we claim to support Perl
> >> 4 in  create_help.pl.  I recall that you Tom once arranged this to
> >> support  building on some old machines.  Is this still relevant?
> 
> > No.
> 
> I concur, there isn't likely to be much demand for building PG >= 8.4
> with Perl 4.

+1

Joshua D. Drake

> 
>             regards, tom lane
> 
--