Table-driven SHOW (was Re: Are we losing momentum?)

[ moving this thread to a more appropriate place ]

Sean Chittenden <sean(at)chittenden(dot)org> writes:
>> It would also be interesting to combine this with Rod's idea of
>> driving describe-type queries by table instead of hardwired code.
>> Imagine that the backend's "show foo" command first looks for "foo"
>> as a GUC variable, as it does now, but upon failing to find one it
>> looks in a system table for a query associated with the name "foo".
>> If it finds such a query, it runs it and sends back the result.
>> Now, not only can we emulate "show tables", but people can easily
>> add application-specific "show whatever" commands, which seems
>> tremendously cool.

> I really like the ability to program in queries or syntaxes into the
> backend, but as it stands, SHOW foo would have to be pretty smart to
> handle the LIKE clauses and other bits.

It's certainly doable. I thought more about how to handle parameters
and such, and came up with this sketch:

1. We generalize the SHOW syntax to accept 1 or more identifiers (might
as well allow strings too). The existing special cases like SHOW TIME
ZONE would be taken out of the grammar and checked for at runtime.

2. The "key" field of the show_queries table is an array of one or more
strings that can be either keywords or parameter placeholders ($n).
There must be at least one keyword. Then SHOW matches a particular
table entry if there are the right number of words and all the keyword
strings match the corresponding words. The other words become the
parameter values.

3. The "query" field of the table is a SELECT possibly containing
parameter references $n. This can be handled the same way as a
preparable statement (we already have mechanisms for resolving the types
of the parameters).

While I haven't studied the MySQL manual to see what-all they allow,
this certainly seems sufficient to support "SHOW TABLE foo" and similar
variants. And the possibility of user-added extensions to the table
seems really cool.

> And how would tab completion be handled in psql?

You look at the table to see what can come after SHOW. We already have
database-driven completion, so this doesn't seem out of reach.

One thing that is doable with psql's current hard-wired approach, but
doesn't seem easy to do with this solution, is automatic localization
of strings such as column headings. Rod had looked at that a little
in his trial patch to convert psql's \d stuff to table-driven form,
but AFAIR he didn't have a satisfactory answer.

regards, tom lane