Re: Proposal: SELECT * EXCLUDE (...) command

Hi Hunaid,

Thanks for adding it to the commitfest. I've added myself as a
reviewer.

> I do agree that we need to limit scope of star_options
> and only use it with target_el. Perhaps, we need to take out '.' '*' from
> indirection_el and refactor some of that part to make it work.

Before going down that road: a few checks in parse analysis might be
simpler and safer. The grammar could stay as it is, with star options
rejected wherever they aren't used. The changes would then stay in a
few functions, while taking '.' '*' out of indirection_el means
reworking how every existing .* is parsed (t.* in expressions,
(expr).*, ROW(t.*), RETURNING t.*, ...). PostgreSQL already rejects
(expr).* inside an expression this way ("row expansion via "*" is not
supported here" in transformIndirection()). As far as I can see, three
checks would cover the cases from my previous mail:

- transformColumnRef(), for t.* inside an expression (function
arguments, WHERE, ORDER BY, ...);
- ExpandColumnRefStar() with make_target_entry = false, for ROW() and
VALUES;
- ExpandIndirectionStar(), for (t2).*.

It would also leave room for supporting some of these cases later.

> I don't have any issue with EXCLUDE clause here, my actual concern
> was using REPLACE clause, since RETURNING would return the
> affected row after UPDATE/DELETE so replacing them with some other
> expression seems odd.

I see the point, but RETURNING already accepts arbitrary expressions,
e.g. RETURNING id, lower(email), and REPLACE is just a shorter way to
write that. One practical case is masking:

UPDATE users SET email = lower(email)
RETURNING * (REPLACE (password WITH '***'));

Allowing EXCLUDE but not REPLACE would also need extra code and a note
in the docs, which currently say that the RETURNING list has the same
syntax as the output list of SELECT.

Also, cfbot's "Linux - Autoconf" job fails: that build enables
ENFORCE_REGRESSION_TEST_NAME_RESTRICTIONS, which warns that roles
created by regression tests should be named regress_*, and the test
creates "tuser". Renaming it, e.g. to regress_staroptions_user, should
fix it.

Regards,
Kacper Kuras