Re: [PATCH] Proposal for HIDDEN/INVISIBLE column

Le 15/10/2021 à 21:52, Andrew Dunstan a écrit :
> On 10/15/21 2:51 PM, Bruce Momjian wrote:
>> On Fri, Oct 15, 2021 at 11:32:53AM +0200, Laurenz Albe wrote:
>>> On Thu, 2021-10-14 at 13:16 +0200, Gilles Darold wrote:
>>>> Here is a proposal to implement HIDDEN columns feature in PostgreSQL.
>>>>
>>>> The user defined columns are always visible in the PostgreSQL. If user
>>>> wants to hide some column(s) from a SELECT * returned values then the
>>>> hidden columns feature is useful. Hidden column can always be used and
>>>> returned by explicitly referring it in the query.
>>> When I read your proposal, I had strangely mixed feelings:
>>> "This is cute!" versus "Do we need that?". After some thinking, I think
>>> that it boils down to the following:
>>>
>>> That feature is appealing to people who type SQL statements into psql,
>>> which is probably the majority of the readers on this list. It is
>>> immediately clear that this can be used for all kinds of nice things.
>>>
>>> On the other hand: a relational database is not a spreadsheet, where
>>> I want to hide or highlight columns. Sure, the interactive user may
>>> use it in that way, but that is not the target of a relational database.
>>> Databases usually are not user visible, but used by an application.
>>> So the appeal for the interactive user is really pretty irrelevant.
>>>
>>> Now this patch makes certain things easier, but it adds no substantially
>>> new functionality: I can exclude a column from display as it is, simply
>>> by listing all the other columns. Sure, that's a pain for the interactive
>>> user, but it is irrelevant for a query in an application.
>>>
>>> This together with the fact that it poses complicated questions when
>>> we dig deeper, such as "what about whole-row references?", tilts my vote.
>>> If it were for free, I would say +1. But given the ratio of potential
>>> headache versus added real-life benefit, I find myself voting -1.
>> I can see the usefulness of this, though UNEXPANDED seems clearer.
>> However, it also is likely to confuse someone who does SELECT * and then
>> can't figure out why another query is showing a column that doesn't
>> appear in SELECT *. I do think SELECT * EXCEPT is the better and less
>> confusing solution. I can imagine people using different EXCEPT columns
>> for different queries, which HIDDEN/UNEXPANDED does not allow. I
>> frankly can't think of a single case where output is specified at the
>> DDL level.
>>
>> Why is this not better addressed by creating a view on the original
>> table, even perhaps renaming the original table and create a view using
>> the old table name.
>
> That's pretty much my feeling. This seems a bit too cute.
>
>
> I have a little function I use to create a skeleton query on tables with
> lots of columns just so I can delete a few and leave the rest, a problem
> that would be solved neatly by the EXCEPT proposal and not but the
> HIDDEN proposal.
>

I have nothing against seeing the EXCEPT included into core except that
this is a big sprain to the SQL standard and I doubt that personally I
will used it for portability reason. Saying that, by this syntax we will
also encourage the use of SELECT * which is incontradiction with the
common opinion.

But again I don't think this is the same feature, the only thing where
SELECT * EXCEPT is useful is for a single non portable statement. It
does not help to extend PostgreSQL through extensions or can solves
application migration issues. I'm a bit surprise by this confusion with
the EXCEPT syntax.

--
Gilles Darold