Specific names for plpgsql variable-resolution control options?

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

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