Re: Can we go beyond the standard to make Postgres radically better?

On 12/02/2022 20:50, Peter J. Holzer wrote:
> On 2022-02-12 01:18:04 +0100, Andreas 'ads' Scherbaum wrote:
>> On 10/02/2022 18:22, Peter J. Holzer wrote:
>>> On 2022-02-09 21:14:39 -0800, Guyren Howe wrote:
>>>> Examples of small things Postgres could have:
>>>>
>>>> • SELECT * - b.a_id from a natural join b
>>> My use case for such a feature are tables which contain one column (or a
>>> small number of columns) which you usually don't want to select: A bytea
>>> column or a very wide text column. In a program I don't mind (in fact I
>>> prefer) listing all the columns explicitely, but exploring a database
>>> interactively with psql typing lots of column names is tedious
>>> (especially since autocomplete doesn't work here).
>> Maybe for this specific use case it's easier to teach psql how to do that,
>> instead of trying to amend the SQL implementation? Example:
>>
>> SELECT * \- col1 \- col2 FROM table
>>
>> psql looks up the columns, translates * into the actual list minus these two
>> columns and lets you continue entering the query.
> I think the easiest way to get the columns would be to EXPLAIN(verbose)
> the query. Otherwise psql (or whatever your shell is) would have to
> completely parse the SQL statement to find the columns.
>
> (On a tangent, I'm wondering if this could work for autocomplete. The
> problem with autocomplete is of course that you probably don't have
> a syntactically correct query at the time you need it. So the editor
> would have to patch that up before sending it to the database.)

I was thinking about this problem for a while, and it's not easy to solve.
Hence I came up with the idea that psql could - once the table is known
and very specific psql syntax is there (\- as example) replace the * with
the actual columns. All of this before the query is run, and as a user you
can edit the column list further.

The main concern listed further upstream is "surfing the database", in
interactive mode. Not the first time I hear this problem.

Solving this specific case might reduce the need for a SQL extenson.

Note: the attempt above is just an idea, not an actual proposal how to
implement this.

Regards,

--
Andreas 'ads' Scherbaum
German PostgreSQL User Group
European PostgreSQL User Group - Board of Directors
Volunteer Regional Contact, Germany - PostgreSQL Project