What is nested subprocedure means:
As the name suggests, it allows us to write a function or procedure nested within another function or procedure. It means a function can have another function within it, just like its own variable. And as this inner function is part of the outer function, it has no visibility outside the function that encloses it. Also, there is no catalog entry created for the nested function. The simplest example will look like:
CREATE OR REPLACE FUNCTION outerfunc(a int) RETURN int IS
FUNCTION innerfunc(b int) RETURN int IS
BEGIN
return b + 1;
END;
BEGIN
return innerfunc(a);
END;
SELECT outerfunc(1);
outerfunc
-----------
2
(1 row)
As explained above, we cannot call innerfunc() from outside, however, it is callable within outerfunc() function body.
Since the nested function is part of the function enclosing it, it can use all variables, types and other nested functions defined in its parent. Also, it can access parent functions parameters. For example,
CREATE OR REPLACE FUNCTION outerfunc(a int) RETURN int IS
v int := 10;
FUNCTION innerfunc(b int) RETURN int IS
BEGIN
return a + b + v;
END;
BEGIN
return innerfunc(a);
END;
SELECT outerfunc(1);
outerfunc
-----------
12
(1 row)
As can be seen above, a nested procedure comes handy when the procedure is needed multiple times in the same procedure but is not required by other procedures, and when the nested procedure requires access to objects declared inside the outer procedure.
In EDB Postgres Advanced Server 9.6, you can write a nested function or procedure within:
- Function
- Procedure
- Trigger
- Anonymous block
A nested function is just like regular function in the sense that,
- They can be called like other functions but within SPL and in scope
- They can accept IN, INOUT or OUT parameters
- They can return sets (SET OF)
- Accept and return polymorphic types
- Recursively call the same function
However, a nested function does not support function level options like a regular function. Which means you cannot specify STRICT or SET options to nested function like you do that for regular function. The nested function is enclosed within another function, so these options are inherited from the root function.
Forward Declaration:
The nested function can call another nested function within its scope. If there are two nested functions sibling to each other, then they can call each other. However when the first function calls the other, it will not find it as that function is not visible yet, resulting in an error.
CREATE OR REPLACE FUNCTION outerfunc(a int) RETURN int IS
FUNCTION innerfunc1(b1 int) RETURN int IS
BEGIN
return innerfunc2(5);
END;
FUNCTION innerfunc2(b2 int) RETURN int IS
BEGIN
return 10;
END;
BEGIN
return innerfunc1(a);
END;
SELECT outerfunc(1);
ERROR: function innerfunc2(integer) does not exist
So to make this work, we need to forward declare the innerfunc2 here, like this:
CREATE OR REPLACE FUNCTION outerfunc(a int) RETURN int IS
FUNCTION innerfunc2(b2 int) RETURN int; -- Forward declaration
FUNCTION innerfunc1(b1 int) RETURN int IS
BEGIN
return innerfunc2(5);
END;
FUNCTION innerfunc2(b2 int) RETURN int IS
BEGIN
return 10;
END;
BEGIN
return innerfunc1(a);
END;
SELECT outerfunc(1);
outerfunc
-----------
10
(1 row)
Function overloading and qualified name resolution:
Nested functions can be overloaded too. So if you don't want to bloat your catalog with many functions with the same name but accepting different arguments, you can enclose them within a single function. This is one kind of encapsulation too. A simple example will look like as:
CREATE OR REPLACE PROCEDURE outerfunc() IS
FUNCTION innerfunc(b1 int) RETURN int IS
BEGIN
return b1;
END;
FUNCTION innerfunc(b1 int, b2 int) RETURN int IS
BEGIN
return b1 + b2;
END;
FUNCTION innerfunc(b1 text) RETURN int IS
BEGIN
return length(b1);
END;
BEGIN
dbms_output.put_line(innerfunc(1));
dbms_output.put_line(innerfunc(10, 20));
dbms_output.put_line(innerfunc('EnterpriseDB'));
END;
EXEC outerfunc;
1
30
12
EDB-SPL Procedure successfully completed
If there are multiple functions with the same name but at a different level, then calling with just the function name is not enough. To call the exact required function, you need to uniquely qualify the function name. For example:
CREATE OR REPLACE PROCEDURE outerfunc() IS
FUNCTION innerfunc(a int) RETURN int IS
FUNCTION nestedfunc(b int) RETURN int IS
FUNCTION innerfunc(c int) RETURN int IS
BEGIN
dbms_output.put_line(' In innerfunc');
IF c = 1 THEN
return innerfunc(2); -- This calls itself
END IF;
return c;
END;
BEGIN
dbms_output.put_line(' In nestedfunc');
return innerfunc(1);
END;
BEGIN
dbms_output.put_line(' In innerfunc');
IF a = 2 THEN
return 2;
END IF;
return nestedfunc(3);
END;
BEGIN
dbms_output.put_line('In outerfunc');
perform innerfunc(1);
END;
EXEC outerfunc;
In outerfunc
In innerfunc
In nestedfunc
In innerfunc
In innerfunc
EDB-SPL Procedure successfully completed
However, if we want to call innerfunc() which is in the outer scope, then we need to qualify that with its parent. Like shown below:
CREATE OR REPLACE PROCEDURE outerfunc() IS
FUNCTION innerfunc(a int) RETURN int IS
FUNCTION nestedfunc(b int) RETURN int IS
FUNCTION innerfunc(c int) RETURN int IS
BEGIN
dbms_output.put_line(' In innerfunc');
IF c = 1 THEN
return outerfunc.innerfunc(2); -- This calls outer innerfunc
END IF;
return c;
END;
BEGIN
dbms_output.put_line(' In nestedfunc');
return innerfunc(1);
END;
BEGIN
dbms_output.put_line(' In innerfunc');
IF a = 2 THEN
return 2;
END IF;
return nestedfunc(3);
END;
BEGIN
dbms_output.put_line('In outerfunc');
perform innerfunc(1);
END;
EXEC outerfunc;
In outerfunc
In innerfunc
In nestedfunc
In innerfunc
In innerfunc
EDB-SPL Procedure successfully completed
Nested function in SQL query:
The nested function cannot be used in an explicit SQL query. The function referred into the SQL query are searched in catalogs.
CREATE OR REPLACE FUNCTION outerfunc(a int) RETURN int IS
v int;
FUNCTION sign(b1 int) RETURN int IS
BEGIN
dbms_output.put_line('Nested function');
return 1;
END;
BEGIN
select sign(a) into v;
return v;
END;
SELECT outerfunc(1);
outerfunc
-----------
1
(1 row)
However, if there is no matching global function found but the nested function, then it throws an error saying "cannot be used here"
CREATE OR REPLACE FUNCTION outerfunc(a int) RETURN int IS
v int;
FUNCTION innerfunc(b1 int) RETURN int IS
BEGIN
dbms_output.put_line('Nested function');
return 1;
END;
BEGIN
select innerfunc(a) into v;
return v;
END;
SELECT outerfunc(1);
ERROR: function innerfunc(integer) cannot be used here
However, calling a nested function in SPL context works well.
CREATE OR REPLACE FUNCTION outerfunc(a int) RETURN int IS
v int;
FUNCTION innerfunc(b1 int) RETURN int IS
BEGIN
dbms_output.put_line('Nested function');
return 1;
END;
BEGIN
v := innerfunc(a);
return v;
END;
SELECT outerfunc(1);
Nested function
outerfunc
-----------
1
(1 row)
Local types within the nested function:
Like packages, you can define local types within a function and that can be used as a parameter or return value in a nested function within the scope. However, unlike packages, these types are not usable outside the function. Here is an example to demonstrate local types usage within the nested function.
CREATE OR REPLACE PROCEDURE outerproc AS
TYPE trec1 IS RECORD (x varchar2(300), y int);
TYPE trec2 IS RECORD (x varchar2(300), y int);
rec1 trec1;
rec2 trec2;
FUNCTION innerfunc(rec trec1) RETURN trec1 AS
BEGIN
dbms_output.put_line('innerfunc: ' || rec);
rec.x := rec.x + 1;
rec.y := rec.y + 1;
return rec;
END;
BEGIN
rec1.x := 1;
rec1.y := 1;
rec2 := innerfunc(rec1);
dbms_output.put_line('outerproc: ' || rec2);
END;
EXEC outerproc;
innerfunc: (1,1)
outerproc: (2,2)
EDB-SPL Procedure successfully completed
Contributions:
I and my colleague Amit Khandekar wrote the patch to support nested subprocedures in EDB Postgres Advanced Server 9.6. Robert Haas committed it after several review rounds. My another colleague from EDB, Prabhat Sahu, tested it.