How to Drop Function/Procedure with checking If it exists in RedShift

Question

In RedShift, I want to do

Drop Function If exists MyFunc(args...);
or
Drop Procedure If Exists MyProc(args...);

It not allow me to do so. But I can do

Drop Function MyFunc(args...);
or
Drop Procedure MyProc(args...);

Why?

I know I can check if the procedure myproc(varchar, varchar) exists by

Select * From pg_proc WHERE proname = 'myproc' And proargtypes = '1043 1043'

But I am not able to use the result. How can I do the same thing as 'if exists'?

Answer 1

This is a bit messy, but you can achieve the same effect with a stored procedure:

CREATE OR REPLACE PROCEDURE conditional_drop_proc(
    schema_name VARCHAR, 
    proc_name VARCHAR, 
    arglist VARCHAR
)
AS $$
    /*
    Drop the procedure with the given name and arguments if it exists, don't fail if it doesn't.

    @param schema_name: The schema in which the procedure lives.
    @param proc_name: The name of the procedure to drop.
    @param arglist: comma-separated list of arg types of the procedure. E.g. varchar,varchar,bool.
    */
DECLARE
    sql_stmt VARCHAR(1000);
    arg_buf VARCHAR(500);
    curr_arg_type VARCHAR(50);
    curr_arg_int INTEGER;
    where_clause VARCHAR(500) := '';
    arg_count INTEGER;
    proc_cnt INTEGER;
BEGIN

    arg_count := regexp_count(arglist, ',') + decode(char_length(arglist) > 0, TRUE, 1, 0);

    /*
    First check if the given procedure exist, and then drop it. To check if it exists, check:
    * its name exists in the given schema
    * the number of arguments passed matches
    * the type of the arguments match
    */
    arg_buf := regexp_replace(arglist, '[[:blank:]]', '');

    FOR i IN 1..arg_count LOOP
        -- Extract next arg
        curr_arg_type := substring(
            arg_buf,
            1,
            CASE
                WHEN charindex(',', arg_buf) = 0
                    THEN char_length(arg_buf)
                ELSE charindex(',', arg_buf) - 1
            END
        );
        arg_buf := substring(arg_buf, charindex(',', arg_buf) + 1);
        SELECT INTO curr_arg_int
            oid
        FROM pg_type
        WHERE typname = curr_arg_type;

        IF NOT FOUND THEN
            RAISE EXCEPTION 'Could not find a type match for %', curr_arg_type;
        END IF;

        where_clause := where_clause || ' AND proc.proargtypes[' || i - 1 || '] = ' || curr_arg_int;
    END LOOP;

    sql_stmt := $stmt$
        SELECT
            1
        from pg_proc proc
        left join pg_namespace ns
            ON proc.pronamespace = ns.oid
        where
            proc.proname = '$stmt$ || proc_name || $stmt$'
            AND ns.nspname = '$stmt$ || schema_name || $stmt$'
            AND proc.pronargs = $stmt$ || arg_count || $stmt$
            $stmt$ || where_clause || $stmt$;
    $stmt$;
    EXECUTE sql_stmt INTO proc_cnt;

    IF (proc_cnt IS NOT NULL) THEN
        sql_stmt := $stmt$
            DROP PROCEDURE $stmt$ || schema_name || '.' || proc_name || '(' || arglist || ')' || $stmt$;
        $stmt$;
        EXECUTE sql_stmt;
        RAISE NOTICE 'Dropped procedure: %', sql_stmt;
    ELSE
        RAISE NOTICE 'Procedure %.%(%) does not exist, skipping.', schema_name, proc_name, arglist;
    END IF;
END;
$$ LANGUAGE plpgsql;

Then just call it with:

CALL conditional_drop_proc('schema', 'proc_name', 'argtyp1,argtyp2,...');