Re: [PATCH] Proposal for HIDDEN/INVISIBLE column

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.

cheers

andrew

--
Andrew Dunstan
EDB: https://www.enterprisedb.com