Skip site navigation (1) Skip section navigation (2)

Re: function_name.parameter_name

From: Bruce Momjian <bruce(at)momjian(dot)us>
To: Sergey Konoplev <gray(dot)ru(at)gmail(dot)com>
Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, "David E(dot) Wheeler" <david(dot)wheeler(at)pgexperts(dot)com>, PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: function_name.parameter_name
Date: 2010-09-08 18:55:54
Message-ID: 201009081855.o88ItsS08553@momjian.us (view raw or flat)
Thread:
Lists: pgsql-hackers
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. +

In response to

Responses

pgsql-hackers by date

Next:From: Magnus HaganderDate: 2010-09-08 19:04:43
Subject: Re: git: uh-oh
Previous:From: Boszormenyi ZoltanDate: 2010-09-08 18:49:03
Subject: Re: plan time of MASSIVE partitioning ...

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group