private, public plpgsql functions

Lot of people does migration from Oracle's PL/SQL to PostgreSQL's PL/pgSQL. One question is how to migrate private functions? There is not a reply. Oracle's PL/SQL is little bit modified language ADA. This language is procedural and modular. The concept of private functions or public functions is very natural there. Nothing similar is in Postgres. Not in this moment. PLpgSQL functions are called by SQL API every time. So visibility for SQL engine should be same for all functions. Theoretically anybody case set SEARCH_PATH for any function manually and then functions from other schema will be invisible. But this design is pretty frail. Somebody can call functions from other schema by qualified name, and the often switch of SEARCH_PATH in significantly decrease readability of your code.

I think so some convention can help. For example, we can mark of any private functions by prefix '_'. With plpgsql_check we can check if these functions are used only inside functions with same schema. The plpgsql_check's function plpgsql_show_dependency_tb does this work.

CREATE SCHEMA packagex;

/*
 * private function, should be used only by functions from this schema
 */
CREATE OR REPLACE FUNCTION packagex._privatefx(a integer)
RETURNS integer AS $$
BEGIN
  RETURN a;
END;
$$ LANGUAGE plpgsql;

/*
 * public function - use private function correctly
 */
CREATE OR REPLACE FUNCTION packagex.publicf1(a integer)
RETURNS integer AS $$
BEGIN
  RETURN packagex._privatefx(a);
END;
$$ LANGUAGE plpgsql;

/*
 * public function - doesn't use private function correctly
 */
CREATE OR REPLACE FUNCTION public.publicf2(a integer)
RETURNS integer AS $$
BEGIN
  RETURN packagex._privatefx(a);
END;
$$ LANGUAGE plpgsql;

The function public.publicf2 is breaking our convention and I have to identify this function.

SELECT *
  FROM (SELECT pronamespace::regnamespace,
               proname,
               x.*
          FROM pg_proc,
               plpgsql_show_dependency_tb(oid) x
         WHERE prolang = (SELECT oid 
                            FROM pg_language 
                           WHERE lanname = 'plpgsql')
           AND pronamespace <> 'pg_catalog'::regnamespace) s
 WHERE starts_with(name, '_')
   AND schema::regnamespace <> pronamespace;

┌──────────────┬──────────┬──────────┬───────┬──────────┬────────────┬───────────┐
│ pronamespace │ proname  │   type   │  oid  │  schema  │    name    │  params   │
╞══════════════╪══════════╪══════════╪═══════╪══════════╪════════════╪═══════════╡
│ public       │ publicf2 │ FUNCTION │ 16411 │ packagex │ _privatefx │ (integer) │
└──────────────┴──────────┴──────────┴───────┴──────────┴────────────┴───────────┘
(1 row)

There is not nice possibility how to implement private functions in Postgres now, but we can introduce some conventions and we can check if these conventions are broken or not.

The function plpgsql_show_dependency_tb display all objects used by function:

SELECT pg_proc.oid::regprocedure AS function, 
       x.oid::regprocedure AS "called function"
  FROM pg_proc,
       plpgsql_show_dependency_tb(oid) x
 WHERE prolang = (SELECT oid 
                    FROM pg_language 
                   WHERE lanname = 'plpgsql')
   AND pronamespace <> 'pg_catalog'::regnamespace 
   AND x.type = 'FUNCTION';
┌────────────────────────────┬──────────────────────────────┐
│          function          │       called function        │
╞════════════════════════════╪══════════════════════════════╡
│ packagex.publicf1(integer) │ packagex._privatefx(integer) │
│ publicf2(integer)          │ packagex._privatefx(integer) │
└────────────────────────────┴──────────────────────────────┘
(2 rows)