Thread: Specific names for plpgsql variable-resolution control options?
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
thetable 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?
regards, tom lane
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? plpgsql_variable_conflict = fatal | oracle-compat | pg-compat ? Best, David
On Fri, Nov 6, 2009 at 3:21 PM, Tom Lane <tgl@sss.pgh.pa.us> 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? I wonder if the word "scoping" might be useful here. ...Robert
"David E. Wheeler" <david@kineticode.com> writes:
> On Nov 6, 2009, at 12:21 PM, Tom Lane wrote:
>> 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.
> plpgsql_variable_conflict = fatal | oracle-compat | pg-compat
plpgsql_variable_conflict is all right, but I think we should avoid
using Oracle's trademarked name in the setting names. I was envisioning
setting names related to "variable first" or "column first" or something
in that line.
regards, tom lane
On Nov 6, 2009, at 4:57 PM, Tom Lane wrote: >> plpgsql_variable_conflict = fatal | oracle-compat | pg-compat > > plpgsql_variable_conflict is all right, but I think we should avoid > using Oracle's trademarked name in the setting names. I was > envisioning > setting names related to "variable first" or "column first" or > something > in that line. That works. plpgsql_variable_conflict = fatal | variable-first | column-first David
"David E. Wheeler" <david@kineticode.com> writes:
> That works.
> plpgsql_variable_conflict = fatal | variable-first | column-first
If we do that, presumably the per-function syntax would be
#variable_conflict variable_first
and so on, which is clear enough but might be thought a bit verbose
for something people might be pasting into hundreds of functions.
regards, tom lane
On Nov 6, 2009, at 6:57 PM, Tom Lane wrote: > If we do that, presumably the per-function syntax would be > > #variable_conflict variable_first > > and so on, which is clear enough but might be thought a bit verbose > for something people might be pasting into hundreds of functions. I suspect that most folks will set the GUC and few will actually use it in functions. Just my guess though. David
2009/11/6 Tom Lane <tgl@sss.pgh.pa.us>: > 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? embeded_sql_identif_priority = sql|embeded|exception regards Pavel Stehule > > regards, tom lane > > -- > Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-hackers >
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@gmail.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
Tom Lane wrote: > "David E. Wheeler" <david@kineticode.com> writes: > > That works. > > > plpgsql_variable_conflict = fatal | variable-first | column-first > > If we do that, presumably the per-function syntax would be > > #variable_conflict variable_first > > and so on, which is clear enough but might be thought a bit verbose > for something people might be pasting into hundreds of functions. If there's some way to "paste" it automatically (like, say, an appropriate UPDATE incantation on pg_proc) then that doesn't seem like an important problem. -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.
Alvaro Herrera <alvherre@commandprompt.com> writes:
> If there's some way to "paste" it automatically (like, say, an
> appropriate UPDATE incantation on pg_proc) then that doesn't seem like
> an important problem.
True, you could do UPDATE pg_proc SET prosrc = 'foo' || prosrc
WHERE something-appropriate.
regards, tom lane
On Fri, Nov 06, 2009 at 07:09:46PM -0800, David E. Wheeler wrote: > On Nov 6, 2009, at 6:57 PM, Tom Lane wrote: > >If we do that, presumably the per-function syntax would be > > #variable_conflict variable_first > >and so on, which is clear enough but might be thought a bit verbose > >for something people might be pasting into hundreds of functions. > > I suspect that most folks will set the GUC and few will actually use > it in functions. Just to be clear about the semantics; what should happen if the user doesn't specify a value for the function? Should PG remember the GUC value at creation time, or pull it in at invocation time? I'd lean towards fixing it at function creation time as it'd be one more caveat for "security definer" functions otherwise. -- Sam http://samason.me.uk/
On Nov 7, 1:21 am, t...@sss.pgh.pa.us (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? > > regards, tom lane > > -- > Sent via pgsql-hackers mailing list (pgsql-hack...@postgresql.org) > To make changes to your subscription:http://www.postgresql.org/mailpref/pgsql-hackers the pspgql oracle softwares donot have the compatibility mode in them so you need to set some prefixes command such as /chkdsk or the manuplating commands which normally come with the software installation and if this doesnot work than contact me at 18:00 hrs tommorow
On Nov 7, 1:21 am, t...@sss.pgh.pa.us (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? > > regards, tom lane > > -- > Sent via pgsql-hackers mailing list (pgsql-hack...@postgresql.org) > To make changes to your subscription:http://www.postgresql.org/mailpref/pgsql-hackers I donot have any further idea about the problem with th oracle software but i do have a problem for you if you will help me out than i will be thankful to you mr Tom lane actually i have downloaded a RAR file type and also used an extracter software but even after extracting the file it is not working properly will you tell me any way by which i can apply and use the file on my lap