Thread: Creating nested functions with plpgsql

Hi!


Is it possible to create nested functions using plpgsql as the language?  I'd
like to avoid other dependencies if I can, besides using already written SQL
code...

There are lots of repetitive tasks that "subfunctions" would solve in a very
elegant way, keeping code more readable and concise.

I was thinking something along the lines of:

CREATE FUNCTION outer_function(param1 DATE) RETURNS date AS $$
DECLARE
BEGIN
    CREATE FUNCTION inner_function(OUT output_day DATE) AS $_$
    DECLARE
        output_day DATE;
    BEGIN
        -- do something to calculate output_day
    END;
    $_$ language plpgsql stable strict;
                          -- here I could have other languages or
                          -- even restrict the inner function to the
                          -- same language as the outer function...

                          -- $_$ is different from $$ intentionally.

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

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.)


TIA,
--
Jorge Godoy      <jgodoy@gmail.com>

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.

Em Domingo 07 Maio 2006 20:33, Alvaro Herrera escreveu:
>
> 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.

Yeah, I know it and that's how I use some things today, but even so, having 
nested functions help to write some kinds of code.

> This is arguably better, because you may then use inner_function in any
> "outer function", not just the current one.

It depends.  There are cases where it is better -- at least for legibility -- 
to have nested functions.  Having a procedural approach is not always the 
best algorithm, but, as I said, it solves almost all problems.


Thanks,
-- 
Jorge Godoy      <jgodoy@gmail.com>