Re: Thoughts on "SELECT * EXCLUDING (...) FROM ..."? - Mailing list pgsql-hackers

From Valentine Gogichashvili
Subject Re: Thoughts on "SELECT * EXCLUDING (...) FROM ..."?
Date
Msg-id CAP93muWZa4qOcJwp6DJQwSzkd-e942BB-FsHpNUo=fmHv-23Bw@mail.gmail.com
Whole thread Raw
In response to Re: Thoughts on "SELECT * EXCLUDING (...) FROM ..."?  (Eric Ridge <eebbrr@gmail.com>)
Responses Re: Thoughts on "SELECT * EXCLUDING (...) FROM ..."?
List pgsql-hackers
On Sun, Oct 30, 2011 at 22:12, Eric Ridge <eebbrr@gmail.com> wrote:

Yes.  It's basically a modifier to the star that immediately precedes
it.  In order to support excluding multiple columns, it needs parens:
    SELECT foo.* EXCLUDING (foo.x, foo.y), bar.* EXCLUDING (bar.y),
baz.z, (a+b) AS c

but yes, that's what I'm thinking.  I think doing this will require
more changes to the grammar than I had first thought because there'd
be no point in supporting:
    SELECT foo.* EXCLUDING (foo.* EXCLUDING foo.y) FROM ...
It looks like the above would be implicitly allowed without a bit of extra work.

But, if you've got a complex query consisting of a few joins, it'd be
nice to say:
    SELECT * EXCLUDING (table1.*, table2.x) FROM table1 INNER JOIN table2 ...


Putting aside arguments like "it is not a good idea to use * because it generates not sustainable code especially in case when you extend table structure",  I think this construct would be really nice for building ROWs, for example in plpgsql triggers or in conditions for big update statements: 

IF (NEW.* EXCLUDING ( last_modified ) ) IS DISTINCT FROM (OLD.* EXCLUDING ( last_modified ) ) THEN NEW.last_modified = clock_timestamp() ; END IF

by now, I do not know any really nice syntax to do that efficiently, and for some wide tables, building this kind of structures listing all the fields, that you have there is completely stupid and makes code unreadable.

So I would definitely like to have a syntax, that you are suggesting in case it would give a possibility to construct ROWs (RECORDs).

Regards, 

-- Valentine Gogichashvili

pgsql-hackers by date:

Previous
From: Brar Piening
Date:
Subject: Re: Is there a good reason we don't have INTERVAL 'infinity'?
Next
From: Simon Riggs
Date:
Subject: Re: Hot Backup with rsync fails at pg_clog if under load