TABLE command

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?

Peter Eisentraut <peter_e(at)gmx(dot)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

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

Peter Eisentraut <peter_e(at)gmx(dot)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

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

"Robert Haas" <robertmhaas(at)gmail(dot)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

> 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

Tom Lane wrote:
> "Robert Haas" <robertmhaas(at)gmail(dot)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.

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?

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(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com

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

David Fetter <david(at)fetter(dot)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

On Tue, 2008-11-18 at 10:36 -0500, Tom Lane wrote:
> David Fetter <david(at)fetter(dot)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
>
--