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

Search Discussions

  • David E. Wheeler at Nov 6, 2009 at 9:57 pm

    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
  • Tom Lane at Nov 7, 2009 at 12:58 am

    "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
  • David E. Wheeler at Nov 7, 2009 at 2:45 am

    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
  • Tom Lane at Nov 7, 2009 at 2:57 am

    "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
  • David E. Wheeler at Nov 7, 2009 at 3:10 am

    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
  • Sam Mason at Nov 9, 2009 at 1:44 am

    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.
  • Alvaro Herrera at Nov 9, 2009 at 12:48 am

    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.
  • Tom Lane at Nov 9, 2009 at 1:17 am

    Alvaro Herrera 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
  • Robert Haas at Nov 6, 2009 at 10:02 pm

    On Fri, Nov 6, 2009 at 3: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?
    I wonder if the word "scoping" might be useful here.

    ...Robert
  • Pavel Stehule at Nov 7, 2009 at 7:24 am

    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
  • Sergio A. Kessler at Nov 7, 2009 at 5:30 pm
    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
  • Tom Lane at Nov 8, 2009 at 1:45 am

    "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
  • Sonu at Nov 9, 2009 at 3:30 pm

    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
  • Sonu at Nov 9, 2009 at 4:00 pm

    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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-hackers @
categoriespostgresql
postedNov 6, '09 at 8:21p
activeNov 9, '09 at 4:00p
posts15
users8
websitepostgresql.org...
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase