Re: Controlling changes in plpgsql variable resolution

On Sun, 2009-10-18 at 13:25 -0400, Tom Lane wrote:

> As most of you will recall, plpgsql currently acts as though identifiers
> in SQL queries should be resolved first as plpgsql variable names, and
> only failing that do they get processed as names of the query. The
> plpgsql parser rewrite that I'm working on will fix that for the
> obviously-silly cases where a plpgsql variable is substituted for a
> table name or some other non-scalar-variable identifier. However, what
> should we do when a name could represent either a plpgsql variable
> or a column of the query? Historically we've resolved it as the
> plpgsql variable, but we've sure heard a lot of complaints about that.
> Oracle's PL/SQL has the precedence the other way around: resolve first
> as the query column, and only failing that as a PL variable. The Oracle
> behavior is arguably less surprising because the query-provided names
> belong to the nearer enclosing scope. I believe that we ought to move
> to the Oracle behavior over time, but how do we get there from here?
> Changing it is almost surely going to break a lot of people's functions,
> and in rather subtle ways.
>
> I think there are basically three behaviors that we could offer:
>
> 1. Resolve ambiguous names as plpgsql (historical PG behavior)
> 2. Resolve ambiguous names as query column (Oracle behavior)
> 3. Throw error if name is ambiguous (useful for finding problems)
>
> (Another possibility is to throw a warning but proceed anyway. It would
> be easy to do that if we proceed with the Oracle behavior, but *not*
> easy if we proceed with the historical PG behavior. The reason is that
> the code invoked by transformColumnRef may have already made some
> side-effects on the query tree. We discussed the implicit-RTE behavior
> yesterday, but there are other effects of a successful name lookup,
> such as marking columns for privilege checking.)
>
> What I'm wondering about at the moment is which behaviors to offer and
> how to control them. The obvious answer is "use a GUC" but that answer
> scares me because of the ease with which switching between #1 and #2
> would break plpgsql functions. It's not out of the question that that
> could even amount to a security problem. I could see using a GUC to
> turn the error behavior (#3) on and off, but not to switch between #1
> and #2.
>
> Another possibility is to control it on a per-function basis by adding
> some special syntax to plpgsql function bodies to say which behavior
> to use. We could for instance extend the never-documented "#option"
> syntax. This is pretty ugly and would be inconvenient to use too
> --- if people have to go and add "#option something" to a function,
> they might as well just fix whatever name conflicts it has instead.

I'd suggest two options, one for name resolution (#1 or #2) and one for
error level of ambiguity (none or ERROR).

GUCs are fine, now we have GUC settings per-function.

--
Simon Riggs www.2ndQuadrant.com