Re: Thoughts on "SELECT * EXCLUDING (...) FROM ..."?

On Sun, Oct 30, 2011 at 22:12, Eric Ridge <eebbrr(at)gmail(dot)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