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

Gazelle

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;$$;
   

Wit

My postgres is on RDS. According to RDS documentation plpgsql_check is not supported – Gazelle

Bad luck. Actually, for SQL functions you can call the validator directly. See my edited answer. – Wit

Fantastic! exactly what I was looking for. What about do $$ begin execute pg_get_functiondef('bad'::regproc); end; $$; - credit to this answer ? – Gazelle

Another thought: I don't see any documentation of this function. Would you say it is "safe" to use on a production DB? Is it guaranteed for backward compatibility in future versions? – Gazelle

pg_get_functiondef probably doesn't check the function. Did you try? fmgr_sql_validator is perfectly safe to use, since this is what runs under the hood when you create an SQL function. There is never a 100% guarantee for upward compatibility, particularly with the internals, but I'm sure this won't change. I personally would not hesitate to use it. – Wit

note the execute before the call to pg_get_functiondef - meaning it executes the creation of the function again - using the source already stored. It does seem to work. however I like your suggestion a lot better. many thanks. ps - if anyone cares here is the source code of fmgr_sql_validator – Gazelle

Oh, that's a smart idea! I'll add it to the answer. – Wit

It looks like select plpgsql_validator('some_plpgsql_func'::regproc) works from sql when the function is indeed plpgsql. The error you posted is when you try to validate an sql function with it - I suggest you edit the answer accordingly. – Gazelle

Oops, will do. Thanks. – Wit

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; $$;

Gazelle