How to work with Nested Subprocedures in PostgreSQL

Jeevan Chalke 2018年8月14日

 

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:

  1. Function
  2. Procedure
  3. Trigger
  4. Anonymous block

 

A nested function is just like regular function in the sense that,

  1. They can be called like other functions but within SPL and in scope
  2. They can accept IN, INOUT or OUT parameters
  3. They can return sets (SET OF)
  4. Accept and return polymorphic types
  5. 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.

Jeevan Chalke

Technical Architect, EnterpriseDB