FROM clause before SELECT

Hi hackers!

At the sessions where I show newcomers around the codebase, we need a
first patch every time: small enough to finish in an hour, in a part of
the DBMSs everyone has an opinion about. The one we usually write makes
this work:

FROM pg_stat_activity SELECT *;

It is a great exercise. You touch the grammar, you learn what bison
means by a conflict, you rebuild, and then the thing actually runs and
prints rows. Then comes my punchline: "of course, this is a non-standard
SQL extension, so it can never go upstream". Everyone nods sadly, and we
move on to something useful.

I have delivered that punchline enough times to start doubting it. The
initial thought is usually to make the grammar more extensible, but that
is a large project without a big win for users. So here is the patch
that I usually throw away, with the parts I usually skip.

0001 lets the FROM clause be written ahead of SELECT, and lets the
SELECT clause be omitted entirely (then it means SELECT *). The other
clauses keep their usual places. It is accepted wherever a SELECT is:
sub-selects, CTEs, set operations, INSERT ... SELECT, and, since FROM is
a reserved word there, in PL/pgSQL statements too. The productions build
exactly the same SelectStmt as the standard spelling, so nothing below
the parser changes and stored queries are displayed the standard way.
There are no new grammar conflicts. DuckDB has had the same syntax for
years and calls it FROM-first [0].

0002 is the part that might be the actual pro-argument. In an off-list
chat Tom pointed out that FROM first would make sense for tab
completion, given a completer a lot smarter than psql is today. It turns
out even today's psql gets somewhere useful. Once the tables are named,
it can complete the columns in the select list itself, which it simply
cannot do for a query that starts with SELECT. The completer only ever
sees the text to the left of the cursor, so in "select c1, <tab> from
t2" the table is on your screen but not in psql's hands. It is not a
missing rule, it is the order of the words. Columns in WHERE and ORDER
BY are completed today already; those rules assume the table name is the
word right before the clause, so they need a small adjustment for the
new shape, and GROUP BY gets column completion it never had. So the gain
is one clause wide, but it is the clause you type first.

What I deliberately did not do: accept the section keywords in any order
at all (Vik's suggestion), or allow SELECT last after GROUP BY (Andreas
Karlsson's). Both are defensible, but let's keep the surface minimal
and tied to the completion argument. Also left out: the rest of the
friendly-SQL menu other systems ship, such as ORDER BY ALL, SELECT *
EXCLUDE and trailing commas. Those are separate discussions.

Tests are in select.sql and plpgsql.sql, psql's completion tests grew
five checks, and the SELECT reference page documents the form and calls
it a PostgreSQL extension in Compatibility.

If the consensus is that our grammar should stay standard-only, that is
a perfectly good answer, and I will restore the punchline for the next
session.

...the real motivation for these patches is "just for fun", of course.

Thank you!

Best regards, Andrey Borodin.

[0] https://duckdb.org/docs/stable/sql/query_syntax/from