Skip site navigation (1) Skip section navigation (2)

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

From: Eric Ridge <eebbrr(at)gmail(dot)com>
To: Robert Haas <robertmhaas(at)gmail(dot)com>
Cc: Jim Nasby <jim(at)nasby(dot)net>, "Joshua D(dot) Drake" <jd(at)commandprompt(dot)com>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: Thoughts on "SELECT * EXCLUDING (...) FROM ..."?
Date: 2011-10-30 19:21:21
Message-ID: CANcm6wY3UJbktoFU5kGRQ9ivNAORhTjRptuEwWTgGw8LHvs4uQ@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-hackers
On Sun, Oct 30, 2011 at 2:54 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:

> OTOH, I'm slightly afraid of how much work it would take to implement
> this properly.

I think first, the A_Star node struct will need to be expanded to
include a List of qualified column references to exclude.  From there,
the "target_el" rule in gram.y will need to be expanded to support a
syntax like:
      '*' EXCLUDING '(' columnref_list ')' { ... }
I also think that the "indirection_el" rule will need to be expanded
to support something similar.

Together, that would let us write both:
     SELECT * EXCLUDING(table1.col1, table2.col1) FROM ...
and
     SELECT table.* EXCLUDING(col1, col2) FROM ...
or even
     SELECT * EXCLUDING(table1.col1), table2.* EXCLUDING(col1) FROM ...

I think changing the "indirection_el" rule might have an impact to
OLD/NEW, but I'm not sure.  Is it legal to write OLD.*, and if so,
would you also want to write OLD.* EXCLUDING (...) in those cases?  I
think this only applies to RULES or SQL-type trigger functions, but
not pl/pgsql?

Then it looks like touching various functions in src/backend/nodes/*.c
to do the right things with the new exclusion list field in A_Star.  I
haven't traced through everything yet, but it looks like if the
various places in src/backend/nodes/*.c are done correctly, then
regurgitating a view definition or whatnot that includes this syntax
will be automatic (i.e., no specific support required for pg_dump)?

Anyways, at first I thought it would be about 8hrs of work just to get
something working.  Maybe it's more like 20, but even still, it seems
fairly straightforward.

eric

In response to

pgsql-hackers by date

Next:From: Darren DuncanDate: 2011-10-30 19:21:28
Subject: Re: Thoughts on "SELECT * EXCLUDING (...) FROM ..."?
Previous:From: Tom LaneDate: 2011-10-30 19:17:53
Subject: Re: Thoughts on "SELECT * EXCLUDING (...) FROM ..."?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group