Date: Sun, 7 May 2006 19:33:43 -0400
From: Alvaro Herrera <alvherre@commandprompt.com>
To: Jorge Godoy <jgodoy@gmail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Creating nested functions with plpgsql
Message-ID: <20060507233343.GC31301@surnet.cl>
Mail-Followup-To: Jorge Godoy <jgodoy@gmail.com>, pgsql-sql@postgresql.org
References: <200605021414.49661.jgodoy@gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <200605021414.49661.jgodoy@gmail.com>
User-Agent: Mutt/1.5.11+cvs20060403

Jorge Godoy wrote:

> I have some real case examples where this could be useful, if it is needed. 
> I haven't pasted them here because the smallest one has 176 LOC, after
> refactoring with nested functions.
> 
> If it is not possible, are there any plans to allow this kind of thing? 
> (Even with a different syntax it would be good to have it.)

We don't support nested functions at present, but you can create a
separate function and invoke it as you would call any external function.
This is arguably better, because you may then use inner_function in any
"outer function", not just the current one.

Something like:

CREATE FUNCTION inner_function(OUT output_day DATE) AS $_$
DECLARE
  output_day DATE;
BEGIN
-- do something to calculate output_day
  RETURN output_day;
END
$_$;

CREATE FUNCTION outer_function(param1 DATE) RETURNS date AS $$
DECLARE
  output_day DATE;
BEGIN;
  output_day = SELECT inner_function(params_to_create_date);

-- do something in main function that uses inner function several
-- times.
END;
$$ language plpgsql;

-- 
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.