How do I create a nested function in PL/pgSQL?

Question

I would like to create a function in PL/pgSQL with a couple of nested (or inner) functions within it. This way I can break the problem down into smaller pieces but not have my smaller pieces accessible outside of this function.

Is it possible to do this in PL/pgSQL? If so, how?

Answer 1

Try it:

CREATE OR REPLACE FUNCTION outer() RETURNS void AS $outer$
DECLARE s text;
BEGIN
  CREATE OR REPLACE FUNCTION inner() RETURNS text AS $inner$
  BEGIN
    RETURN 'inner';
  END;
  $inner$ language plpgsql;

  SELECT inner() INTO s;
  RAISE NOTICE '%', s;

  DROP FUNCTION inner();
END;
$outer$ language plpgsql;

In postgres 9.5 SELECT outer(); outputs

 psql:/vagrant/f.sql:14: NOTICE:  inner

EDIT: if you don't drop the inner function at the end of the outer function it will remain visible to the rest of the database.