Howdy,

Anyone ever thought to try to add $subject to PL/pgSQL? Someone left a [comment][] on the PGXN blog about how this is a supported syntax for using named parameters on Oracle. The context is to avoid conflicts between variable names and column names by function-qualifyin the former and table-qualifying the latter.

[comment]: http://blog.pgxn.org/post/1053165383/alias-in-vogue#dsq-comment-75687336

Would this be do-able in PL/pgSQL?

Best,

David

Search Discussions

  • Tom Lane at Sep 7, 2010 at 4:35 pm

    "David E. Wheeler" <david.wheeler@pgexperts.com> writes:
    Anyone ever thought to try to add $subject to PL/pgSQL?
    How does $subject differ from what we already do? See
    http://www.postgresql.org/docs/9.0/static/plpgsql-structure.html
    particularly this:

    Note: There is actually a hidden "outer block" surrounding the
    body of any PL/pgSQL function. This block provides the
    declarations of the function's parameters (if any), as well as
    some special variables such as FOUND (see Section 39.5.5). The
    outer block is labeled with the function's name, meaning that
    parameters and special variables can be qualified with the
    function's name.


    regards, tom lane
  • David E. Wheeler at Sep 7, 2010 at 4:40 pm

    On Sep 7, 2010, at 9:35 AM, Tom Lane wrote:

    How does $subject differ from what we already do? See
    http://www.postgresql.org/docs/9.0/static/plpgsql-structure.html
    particularly this:

    Note: There is actually a hidden "outer block" surrounding the
    body of any PL/pgSQL function. This block provides the
    declarations of the function's parameters (if any), as well as
    some special variables such as FOUND (see Section 39.5.5). The
    outer block is labeled with the function's name, meaning that
    parameters and special variables can be qualified with the
    function's name.
    Well I'll be damned. I never knew about this! So I can get rid of those aliases!

    http://github.com/theory/pgxn-manager/commit/e5add190ff5358a0b2ede64b62616491be454c50

    Thanks Tom, I had *no idea* about this.

    Best,

    David
  • Sergey Konoplev at Sep 7, 2010 at 6:40 pm
    Hi,
    On 7 September 2010 20:35, Tom Lane wrote:
    How does $subject differ from what we already do?  See
    http://www.postgresql.org/docs/9.0/static/plpgsql-structure.html
    So will it be possible to do things like this?

    1.
    CREATE FUNCTION func_name(arg_name text) RETURNS integer AS $$
    BEGIN
    RAISE INFO '%', func_name.arg_name;
    ...

    2.
    CREATE FUNCTION func_name() RETURNS integer AS $$
    DECLARE
    var_name text := 'bla';
    BEGIN
    RAISE INFO '%', func_name.var_name;
    ...

    3.
    CREATE FUNCTION func_very_very_very_very_long_name() RETURNS integer AS $$
    << func_alias >>
    DECLARE
    var_name text := 'bla';
    BEGIN
    RAISE INFO '%', func_alias.var_name;
    ...


    --
    Sergey Konoplev

    Blog: http://gray-hemp.blogspot.com /
    Linkedin: http://ru.linkedin.com/in/grayhemp /
    JID/GTalk: gray.ru@gmail.com / Skype: gray-hemp / ICQ: 29353802
  • David E. Wheeler at Sep 7, 2010 at 9:13 pm
    I think so. Try it!

    David
    On Sep 7, 2010, at 11:39 AM, Sergey Konoplev wrote:

    Hi,
    On 7 September 2010 20:35, Tom Lane wrote:
    How does $subject differ from what we already do? See
    http://www.postgresql.org/docs/9.0/static/plpgsql-structure.html
    So will it be possible to do things like this?

    1.
    CREATE FUNCTION func_name(arg_name text) RETURNS integer AS $$
    BEGIN
    RAISE INFO '%', func_name.arg_name;
    ...

    2.
    CREATE FUNCTION func_name() RETURNS integer AS $$
    DECLARE
    var_name text := 'bla';
    BEGIN
    RAISE INFO '%', func_name.var_name;
    ...

    3.
    CREATE FUNCTION func_very_very_very_very_long_name() RETURNS integer AS $$
    << func_alias >>
    DECLARE
    var_name text := 'bla';
    BEGIN
    RAISE INFO '%', func_alias.var_name;
  • Bruce Momjian at Sep 8, 2010 at 6:56 pm

    Sergey Konoplev wrote:
    Hi,
    On 7 September 2010 20:35, Tom Lane wrote:
    How does $subject differ from what we already do? ?See
    http://www.postgresql.org/docs/9.0/static/plpgsql-structure.html
    So will it be possible to do things like this?

    1.
    CREATE FUNCTION func_name(arg_name text) RETURNS integer AS $$
    BEGIN
    RAISE INFO '%', func_name.arg_name;
    ...

    2.
    CREATE FUNCTION func_name() RETURNS integer AS $$
    DECLARE
    var_name text := 'bla';
    BEGIN
    RAISE INFO '%', func_name.var_name;
    ...

    3.
    CREATE FUNCTION func_very_very_very_very_long_name() RETURNS integer AS $$
    << func_alias >>
    DECLARE
    var_name text := 'bla';
    BEGIN
    RAISE INFO '%', func_alias.var_name;
    ...
    In my testing #1 works, but #2 does not:

    -- #1
    test=> CREATE OR REPLACE FUNCTION xxx(yyy INTEGER) RETURNS void AS $$
    BEGIN
    xxx.yyy := 4;
    END;$$
    LANGUAGE plpgsql;
    CREATE FUNCTION

    -- #2
    test=> CREATE OR REPLACE FUNCTION xxx() RETURNS void AS $$
    DECLARE yyy integer;
    BEGIN
    xxx.yyy := 4;
    END;$$
    LANGUAGE plpgsql;
    ERROR: "xxx.yyy" is not a known variable
    LINE 3: xxx.yyy := 4;
    ^

    #2 works only if you specify a label above the DECLARE section and use
    that label (not the function name) as a variable qualifier:

    test=> CREATE OR REPLACE FUNCTION xxx() RETURNS void AS $$
    <<zzz>>
    DECLARE yyy INTEGER;
    BEGIN
    zzz.yyy := 4;
    END;$$
    LANGUAGE plpgsql;
    CREATE FUNCTION

    Interestingly, I can use a label that matches the function name:

    test=> CREATE OR REPLACE FUNCTION xxx() RETURNS void AS $$
    <<xxx>>
    DECLARE yyy INTEGER;
    BEGIN
    xxx.yyy := 4;
    END;$$
    LANGUAGE plpgsql;
    CREATE FUNCTION

    but if you supply parameters to the function, it does not work:

    test=> CREATE OR REPLACE FUNCTION xxx(aaa INTEGER) RETURNS void AS $$
    <<xxx>>
    DECLARE yyy INTEGER;
    BEGIN
    xxx.yyy := 4;
    END;$$
    LANGUAGE plpgsql;
    ERROR: cannot change name of input parameter "yyy"
    HINT: Use DROP FUNCTION first.

    so this is not something we can recommend to users.

    Note the text Tom quoted from our docs:

    http://www.postgresql.org/docs/9.0/static/plpgsql-structure.html

    There is actually a hidden <quote>outer block</> surrounding
    the body of any <application>PL/pgSQL</> function. This block
    provides the declarations of the function's parameters (if any),
    as well as some special variables such as <literal>FOUND</literal>
    (see <xref linkend="plpgsql-statements-diagnostics">). The
    outer block is labeled with the function's name, meaning that
    parameters and special variables can be qualified with the
    function's name.

    This talks about the parameters, but not about the DECLARE block.

    The idea of adding a label to DECLARE blocks is mentioned in our docs:

    http://www.postgresql.org/docs/9.0/static/plpgsql-implementation.html#PLPGSQL-VAR-SUBST

    Alternatively you can qualify ambiguous references to make them clear.
    In the above example, src.foo would be an unambiguous reference to the
    table column. To create an unambiguous reference to a variable, declare
    it in a labeled block and use the block's label (see Section 39.2).

    --
    Bruce Momjian <bruce@momjian.us> http://momjian.us
    EnterpriseDB http://enterprisedb.com

    + It's impossible for everything to be true. +
  • Tom Lane at Sep 8, 2010 at 7:16 pm

    Bruce Momjian writes:
    ... but if you supply parameters to the function, it does not work:
    test=> CREATE OR REPLACE FUNCTION xxx(aaa INTEGER) RETURNS void AS $$
    ERROR: cannot change name of input parameter "yyy"
    HINT: Use DROP FUNCTION first.
    This is failing because you tried to redeclare xxx(int) with a different
    name for its parameter, which is no longer allowed. It has nothing to
    do with the question at hand.

    regards, tom lane
  • Darren Duncan at Sep 8, 2010 at 7:23 pm

    Bruce Momjian wrote:
    Sergey Konoplev wrote:
    1.
    CREATE FUNCTION func_name(arg_name text) RETURNS integer AS $$
    BEGIN
    RAISE INFO '%', func_name.arg_name;
    ...

    2.
    CREATE FUNCTION func_name() RETURNS integer AS $$
    DECLARE
    var_name text := 'bla';
    BEGIN
    RAISE INFO '%', func_name.var_name;
    ...

    3.
    CREATE FUNCTION func_very_very_very_very_long_name() RETURNS integer AS $$
    << func_alias >>
    DECLARE
    var_name text := 'bla';
    BEGIN
    RAISE INFO '%', func_alias.var_name;
    ...
    I suggest that it might be reasonable to introduce a new syntax, that isn't
    already valid for something inside a routine, and use that as a terse way to
    reference the current function and/or its parameters. This may best be a simple
    constant syntax.

    For example, iff it isn't already valid for a qualified name to have a leading
    period/full-stop/radix-marker, then this could be introduced as a valid way to
    refer to the current routine.

    Then in the above examples you can say:

    RAISE INFO '%', .arg_name;

    RAISE INFO '%', .var_name;

    ... without explicitly declaring a func_alias.

    In a tangent, you can also use a new constant syntax (unless you have one?) to
    allow a routine to invoke itself without knowing its own name, which could be
    nice in a simple recursive routine. Maybe ".(arg,arg)" would do it?

    I would think this should be non-intrusive and useful and could go in 9.1.

    -- Darren Duncan
  • Robert Haas at Sep 8, 2010 at 9:09 pm

    On Sep 8, 2010, at 3:17 PM, Darren Duncan wrote:
    Bruce Momjian wrote:
    Sergey Konoplev wrote:
    1.
    CREATE FUNCTION func_name(arg_name text) RETURNS integer AS $$
    BEGIN
    RAISE INFO '%', func_name.arg_name;
    ...

    2.
    CREATE FUNCTION func_name() RETURNS integer AS $$
    DECLARE
    var_name text := 'bla';
    BEGIN
    RAISE INFO '%', func_name.var_name;
    ...

    3.
    CREATE FUNCTION func_very_very_very_very_long_name() RETURNS integer AS $$
    << func_alias >>
    DECLARE
    var_name text := 'bla';
    BEGIN
    RAISE INFO '%', func_alias.var_name;
    ...
    I suggest that it might be reasonable to introduce a new syntax, that isn't already valid for something inside a routine, and use that as a terse way to reference the current function and/or its parameters. This may best be a simple constant syntax.
    This has been proposed in the past and Tom has rejected it, but I agree that it would be useful. The key word in this proposal is "terse".

    ...Robert
  • Darren Duncan at Sep 8, 2010 at 9:41 pm

    Robert Haas wrote:
    On Sep 8, 2010, at 3:17 PM, Darren Duncan wrote:
    Bruce Momjian wrote:
    Sergey Konoplev wrote:
    3.
    CREATE FUNCTION func_very_very_very_very_long_name() RETURNS integer AS $$
    << func_alias >>
    DECLARE
    var_name text := 'bla';
    BEGIN
    RAISE INFO '%', func_alias.var_name;
    ...
    I suggest that it might be reasonable to introduce a new syntax, that isn't already valid for something inside a routine, and use that as a terse way to reference the current function and/or its parameters. This may best be a simple constant syntax.
    This has been proposed in the past and Tom has rejected it, but I agree that it would be useful. The key word in this proposal is "terse".
    Absolutely.

    In fact I'm not particularly enamored with my ".foo" example suggestion because
    I would actually prefer for that particular syntax to be left unused and
    available for other possible future uses that are better thought out.

    I think instead that something akin to an explicit alias would both be more
    future-proofed and be the least surprising to existing users, as per #3. If the
    alias was very short, then we have something terse for usage.

    I should also say that this subject has some bearing on the topic of aliases or
    synonyms in general. In the situations where one wants an entity to be
    referenceable by more than one name, and knows this at the time of declaring
    said entity, there could be a syntax for declaring the extra names inline with
    the original.

    For example, if it wouldn't conflict with anything, one could use the "|" symbol
    (mnemonic is that means "alternation" in regular expressions) like this:

    CREATE FUNCTION func_very_very_very_very_long_name|short_name() ...

    ... but this could use some work since I also see that being useful for
    declaring synonyms inline, which are public names like the original, not just
    internal private names. When used for synonyms, this would still be represented
    in the system catalog as a function named func_very... and a synonym named
    short_name, this synonym being akin to a Unix soft link or a C symbolic alias in
    semantics.

    Similarly, and mainly for use with named argument syntax, a named parameter
    could have several names it could go by, declared with | also. Example:

    CREATE FUNCTION func_name(arg_name|altnm text) ...

    It doesn't have to be that syntax, but I demonstrated a principle, and I
    personally like "|" for the mnemonic.

    -- Darren Duncan
  • Alvaro Herrera at Sep 8, 2010 at 10:08 pm

    Excerpts from Darren Duncan's message of mié sep 08 17:41:40 -0400 2010:

    For example, if it wouldn't conflict with anything, one could use the "|" symbol
    (mnemonic is that means "alternation" in regular expressions) like this:

    CREATE FUNCTION func_very_very_very_very_long_name|short_name() ...
    If you can name the function short_name, why not use just that in the
    first place?

    --
    Álvaro Herrera <alvherre@commandprompt.com>
    The PostgreSQL Company - Command Prompt, Inc.
    PostgreSQL Replication, Consulting, Custom Development, 24x7 support
  • Darren Duncan at Sep 8, 2010 at 10:29 pm

    Alvaro Herrera wrote:
    Excerpts from Darren Duncan's message of mié sep 08 17:41:40 -0400 2010:
    For example, if it wouldn't conflict with anything, one could use the "|" symbol
    (mnemonic is that means "alternation" in regular expressions) like this:

    CREATE FUNCTION func_very_very_very_very_long_name|short_name() ...
    If you can name the function short_name, why not use just that in the
    first place?
    More realistic examples would be either of:

    1. Offer users the choice of a longer more self-describing name and a terser
    name. For example: "function is_member_of|in (...) ...".

    2. Offer users the choice of similar length but different names. For example:
    "function sum|add(x integer, y integer) returns integer ...".

    3. Make it easier to change your mind on a name while providing backwards
    compatibility for awhile. For example: "function new_name|old_name (...) ...".

    Personally I like the idea of developers not always having to be forced to
    choose among two equally good names, and making a wrapper function would be
    overkill for this feature.

    -- Darren Duncan
  • Alvaro Herrera at Sep 8, 2010 at 10:35 pm

    Excerpts from Darren Duncan's message of mié sep 08 18:29:35 -0400 2010:

    Personally I like the idea of developers not always having to be forced to
    choose among two equally good names, and making a wrapper function would be
    overkill for this feature.
    While I don't agree with the idea of providing extra names that are
    probably mostly going to increase the confusion of someone trying to
    understand such a system, I think this use case would be well covered by
    synonyms. But these would be defined by a new SQL command, say CREATE
    SYNONYM, not by funny notation on the initial CREATE FUNCTION call.

    --
    Álvaro Herrera <alvherre@commandprompt.com>
    The PostgreSQL Company - Command Prompt, Inc.
    PostgreSQL Replication, Consulting, Custom Development, 24x7 support
  • Darren Duncan at Sep 8, 2010 at 10:57 pm

    Alvaro Herrera wrote:
    Excerpts from Darren Duncan's message of mié sep 08 18:29:35 -0400 2010:
    Personally I like the idea of developers not always having to be forced to
    choose among two equally good names, and making a wrapper function would be
    overkill for this feature.
    While I don't agree with the idea of providing extra names that are
    probably mostly going to increase the confusion of someone trying to
    understand such a system, I think this use case would be well covered by
    synonyms. But these would be defined by a new SQL command, say CREATE
    SYNONYM, not by funny notation on the initial CREATE FUNCTION call.
    Yes, and having a more general solution like CREATE SYNONYM is more important to
    have anyway. My "|" is simply a syntactic shorthand for a special case of
    CREATE SYNONYM, with respect to schema objects, and would parse into the same
    thing. I don't feel any need now for me to push this shorthand further. --
    Darren Duncan
  • David E. Wheeler at Sep 9, 2010 at 4:12 pm

    On Sep 8, 2010, at 3:57 PM, Darren Duncan wrote:

    While I don't agree with the idea of providing extra names that are
    probably mostly going to increase the confusion of someone trying to
    understand such a system, I think this use case would be well covered by
    synonyms. But these would be defined by a new SQL command, say CREATE
    SYNONYM, not by funny notation on the initial CREATE FUNCTION call.
    Sounds handy.
    Yes, and having a more general solution like CREATE SYNONYM is more important to have anyway. My "|" is simply a syntactic shorthand for a special case of CREATE SYNONYM, with respect to schema objects, and would parse into the same thing. I don't feel any need now for me to push this shorthand further. -- Darren Duncan
    I can't get excited about it. I'm just happy the functionality is there. I was able to both simplify my PL/pgSQL code *and* make it much clearer what it's doing:

    http://github.com/theory/pgxn-manager/commit/e136ccb342010e836c39dafa43b802478be445a0

    That said, I'm assuming that the function-name block is really a RECORD object representing the argument signature. I could see a case for PL/pgSQL just having an "ARGS" variable or something that does the same thing. Kind of like triggers have NEW and OLD. But given that the functionality is already there, that's just gravy. Or sugar. I'm not sure which. Sugary gravy.

    Best,

    David

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-hackers @
categoriespostgresql
postedSep 7, '10 at 4:24p
activeSep 9, '10 at 4:12p
posts15
users8
websitepostgresql.org...
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase