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