How to re-check an SQL function created with check_function_bodies=false?

Question

Following this answer I learnt that I can disable syntax checking for an SQL function by setting check_function_bodies=false.

In my case: Running an upgrade using Flyway where

1. The order of function creation is not well defined
2. Some functions use other functions yet to be created within the same upgrade.

My question is - is it possible to issue a "recheck" of such a function without having to actually call it - once all other dependencies are in place? Something similar to Oracle's alter function ... compile.

Ideally I would like to set check_function_bodies=false at the beginning of the upgrade and then recheck every SQL function at the end of it.

I want to avoid having to:

1. control the order in which scripts are run.
2. re-run the function creation scripts

Things I tried:

• Doing a dummy alter function
• Calling pg_get_functiondef

Answer 1

I can think of two ways:

1. You can call the language validator function directly:

   SELECT lanname, lanvalidator::regprocedure FROM pg_language;
   
     lanname   |         lanvalidator         
   ------------+------------------------------
    internal   | fmgr_internal_validator(oid)
    c          | fmgr_c_validator(oid)
    sql        | fmgr_sql_validator(oid)
    plpgsql    | plpgsql_validator(oid)
    plpython3u | plpython3_validator(oid)
   (5 rows)
   

   For SQL functions, that would work like this:

   SET check_function_bodies = off;
   CREATE FUNCTION bad() RETURNS void LANGUAGE sql AS 'SELECT $1';
   
   SET check_function_bodies = on;
   SELECT fmgr_sql_validator('bad()'::regprocedure);
   
   ERROR:  there is no parameter $1
   LINE 1: SELECT $1
                  ^
   QUERY:  SELECT $1
   

2. You can redefine the function and check if it throws an error:

   SET check_function_bodies = on;
   DO $$BEGIN
      EXECUTE pg_get_functiondef('bad()'::regprocedure);
   END;$$;
   

Answer 2

Following Laurenz' brilliant answer I wrote this little helper function - sharing for the benefit of others.

CREATE OR REPLACE FUNCTION recompile_functions()
RETURNS void
LANGUAGE plpgsql
VOLATILE
AS $$
DECLARE
    l_func regproc;
BEGIN
    --schema name can also be an input param or current_schema.
    --test sql functions
    FOR l_func IN (
        SELECT oid
        FROM pg_proc
        WHERE pronamespace='my_schema'::regnamespace
        AND prolang=(SELECT oid FROM pg_language WHERE lanname='sql')
    ) 
    LOOP
        PERFORM fmgr_sql_validator(l_func);
    END LOOP;

    --test plpgsql functions
    FOR l_func IN (
        SELECT oid
        FROM pg_proc
        WHERE pronamespace='my_schema'::regnamespace
        AND prolang=(SELECT oid FROM pg_language WHERE lanname='plpgsql')
    ) 
    LOOP
        PERFORM plpgsql_validator(l_func);
    END LOOP;

EXCEPTION
    WHEN OTHERS THEN
        RAISE EXCEPTION 'Function % failed validation checks: %', l_func::text, SQLERRM;
END; $$;