Specific names for plpgsql variable-resolution control options?

[Tom Lane-2]

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-hackers@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[David E. Wheeler]

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[Robert Haas]

On Fri, Nov 6, 2009 at 3:21 PM, Tom Lane <tgl@...> 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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[Tom Lane-2]

"David E. Wheeler" <david@...> 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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[David E. Wheeler]

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[Tom Lane-2]

"David E. Wheeler" <david@...> 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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[David E. Wheeler]

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[Pavel Stehule]

2009/11/6 Tom Lane <tgl@...>:

> 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@...)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>

--
Sent via pgsql-hackers mailing list (pgsql-hackers@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[Sergio A. Kessler-2]

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[Tom Lane-2]

"Sergio A. Kessler" <sergiokessler@...> 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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[Alvaro Herrera-7]

Tom Lane wrote:

> "David E. Wheeler" <david@...> 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.

--
Sent via pgsql-hackers mailing list (pgsql-hackers@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[Tom Lane-2]

Alvaro Herrera <alvherre@...> 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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[Sam Mason]

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/

--
Sent via pgsql-hackers mailing list (pgsql-hackers@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[Sonu-8]

On Nov 7, 1:21 am, t...@... (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...@...)
> 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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[Sonu-8]

On Nov 7, 1:21 am, t...@... (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...@...)
> 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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers