Content-Addressed Functions in PostgreSQL

create_or_lookup_function.sql

--
-- Content-Addressed Functions
--
-- Inspired by Unison's concept of using a hash of a function's syntax tree
-- as its name, sometimes referred to as "content-addressed naming",
-- we can avoid the need to create/drop lots of different temp function,
-- by using the hash of its definition as its name,
-- and simply reuse the function for all tests that need the same function.
--
-- While Unison uses 512-bit SHA3, we use sha224() since the maximum length
-- for a function name in PostgreSQL is 63 characters, so the hex of sha512()
-- wouldn't fit.
--
CREATE OR REPLACE FUNCTION create_or_lookup_function(
    argtypes text[],
    function_definition text,
    rettype text
)
RETURNS text
LANGUAGE plpgsql
AS $$
declare

    function_name text;

begin

    function_name := encode(sha224(convert_to(format(
        '%L%L%L',
        argtypes::text,
        function_definition,
        rettype
    ),'utf8')),'hex');

    if not exists (select 1 from pg_proc where proname = function_name) then
        --
        -- Also use the hash as the dollar quoting tag,
        -- which will eliminate the risk of conflicting dollar quoting
        -- inside the code, since the code is part of the hash.
        --
        execute format(
            $_$
                CREATE OR REPLACE FUNCTION pg_temp."%1$s"(%2$s)
                RETURNS %3$s
                LANGUAGE plpgsql
                AS
                $_%1$s_$
                    %4$s
                $_%1$s_$;
            $_$,
            function_name,
            array_to_string(argtypes,','),
            rettype,
            function_definition
        );
    end if;

    return function_name;

end
$$;

/*

Example:

SELECT create_or_lookup_function('{numeric,numeric}','begin return $1+$2; end','numeric');
                create_or_lookup_function
----------------------------------------------------------
 08a7bd67801f0490032a3599c4b1e6af2d4d7cd3eb4ae16a1ed0c590
(1 row)

SELECT pg_temp."08a7bd67801f0490032a3599c4b1e6af2d4d7cd3eb4ae16a1ed0c590"(10,20);
 08a7bd67801f0490032a3599c4b1e6af2d4d7cd3eb4ae16a1ed0c590
----------------------------------------------------------
                                                       30
(1 row)

*/