DROP SCHEMA admin CASCADE;

CREATE SCHEMA admin;

create or replace function admin.fn_show_functions(text)
returns setof text as $$
declare
    v_schema alias for $1;
    v_schema_oid oid;
    v_function pg_catalog.pg_proc%rowtype;
    v_function_arg text;
    v_function_name_and_args text;
begin
    select into v_schema_oid oid
    from pg_catalog.pg_namespace
    where nspname = v_schema;

    if found then
        for v_function in select * from pg_catalog.pg_proc
        where pronamespace = v_schema_oid
        loop
            v_function_name_and_args := v_function.proname || '(';

            for i in 0..(v_function.pronargs - 1)
            loop
                select into v_function_arg typname
                from pg_catalog.pg_type
                where oid = v_function.proargtypes[i];

                if v_function_arg is not null then
                    v_function_name_and_args := v_function_name_and_args || v_function_arg || ', ';
                end if;
            end loop;

            v_function_name_and_args := trim(trailing ', ' from v_function_name_and_args);
            v_function_name_and_args := v_function_name_and_args || ')';

            return next v_function_name_and_args;
        end loop;
    end if;

    return;
end;
$$
language plpgsql;

create or replace function admin.fn_revoke_all(text)
returns void as $$
declare
    v_user alias for $1;
    v_schema record;
    v_obj record;
    v_current_db text;
begin
    -- Second, revoke on functions, tables, and views from
    -- user schemas.
    for v_schema in select nspname AS name FROM pg_namespace WHERE nspname NOT LIKE 'pg%' AND nspname NOT LIKE 'info%'
    loop
        perform admin.fn_revoke_all_functions_from(v_user, v_schema.name); 
    end loop;

end;
$$
language plpgsql;

create or replace function admin.fn_revoke_all_functions_from(text, text)
returns void as $$
declare
    v_user alias for $1;
    v_schema alias for $2;
    v_obj record;
begin
        for v_obj in select * from admin.fn_show_functions(v_schema) as name
        loop
            --raise notice 'revoking function %', v_obj.name;
            execute 'revoke all on function ' ||
            quote_ident(v_schema) || '."' ||
            replace(v_obj.name, '(', '"(') || ' from ' ||
            quote_ident(v_user);
        end loop;
end;
$$
language plpgsql;


SELECT admin.fn_revoke_all('public');
-- Sometimes doesn't crash on the first attempt
SELECT admin.fn_revoke_all('public');
SELECT admin.fn_revoke_all('public');
SELECT admin.fn_revoke_all('public');