Re: function_name.parameter_name

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

"David E. Wheeler" <david(dot)wheeler(at)pgexperts(dot)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

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

Hi,

On 7 September 2010 20:35, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> 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(dot)ru(at)gmail(dot)com / Skype: gray-hemp / ICQ: 29353802

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 <tgl(at)sss(dot)pgh(dot)pa(dot)us> 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 wrote:
> Hi,
>
> On 7 September 2010 20:35, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> 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(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +

Bruce Momjian <bruce(at)momjian(dot)us> 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

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

On Sep 8, 2010, at 3:17 PM, Darren Duncan <darren(at)darrenduncan(dot)net> 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

Robert Haas wrote:
> On Sep 8, 2010, at 3:17 PM, Darren Duncan <darren(at)darrenduncan(dot)net> 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

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(at)commandprompt(dot)com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

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

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(at)commandprompt(dot)com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

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

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