Re: Specific names for plpgsql variable-resolution control options?

hi tom, sorry for the out-of-the-blue email (I'm not on the list)...

On Nov 6, 2009, at 12:21 PM, Tom Lane wrote:

> I believe we had consensus that plpgsql should offer the following
> three
> behaviors when a name in a SQL query could refer to either a plpgsql
> variable or a column from a table of the query:
> * prefer the plpgsql variable (plpgsql's historical behavior)
> * prefer the table column (Oracle-compatible)
> * throw error for the ambiguity (to become the factory default)
> and that we wanted a way for users to select one of these behaviors
> at the
> per-function level, plus provide a SUSET GUC to determine the default
> behavior when there is not a specification in the function text.
>
> What we did not have was any concrete suggestions for the name or
> values of the GUC, nor for the exact per-function syntax beyond the
> thought that it could look something like the existing '#option dump'
> modifier.
>
> The code is now there and ready to go, so I need a decision on these
> user-visible names in order to proceed. Anyone have ideas?

is this become configurable somehow,
how would I know that my code work as expected when I distribute my code ?

one option is to put
foo_variable_conflict = error
throughout the code, which can be thousands of lines, which is not
nice just to be sure my code works as expected no matter what...
(setting a general GUC can interfere with another code, which presumes
different things)

and moreover, is a burden for postgresql that should be supporting
'foo_variable_conflict' in the foreseeable future...

IMO, postgres should stick with one option (+1 for error) and be done
with this, just one simple rule to rule them all...
and with this, there is no need to band-aid the code just in case...

regards,
/sergio

"Sergio A. Kessler" <sergiokessler(at)gmail(dot)com> writes:
> On Nov 6, 2009, at 12:21 PM, Tom Lane wrote:
>> I believe we had consensus that plpgsql should offer the following
>> three
>> behaviors when a name in a SQL query could refer to either a plpgsql
>> variable or a column from a table of the query:
>> * prefer the plpgsql variable (plpgsql's historical behavior)
>> * prefer the table column (Oracle-compatible)
>> * throw error for the ambiguity (to become the factory default)
>> and that we wanted a way for users to select one of these behaviors
>> at the
>> per-function level, plus provide a SUSET GUC to determine the default
>> behavior when there is not a specification in the function text.

> is this become configurable somehow,
> how would I know that my code work as expected when I distribute my code ?

If you're sufficiently worried about that, you can put the
about-to-be-selected option syntax at the start of every function.
Bear in mind though that there are many many ways for unexpected
environmental settings to break functions (search_path being one
of the more obvious ones); I'm not sure this one is any worse than
the rest. Especially not if you test under the default 'raise error
on conflict' setting. I think the other two values will mainly be
useful for legacy code of one persuasion or the other.

regards, tom lane