How to extract duplicate logic in a Postgres function?

Question

I have a Postgres function with lots of duplicate logic. If I wrote this in, say, Ruby, I would extract duplicate logic into several private helper methods. But in Postgres there is no equivalent to "private methods".

Original Function

CREATE OR REPLACE FUNCTION drop_create_idx_constraint(in_operation varchar, in_table_name_or_all_option varchar)  RETURNS integer AS $$
DECLARE
    cur_drop_for_specific_tab CURSOR (tab_name varchar) IS SELECT drop_stmt FROM table_indexes WHERE table_indexes.table_name = table_name_to_drop;
    cur_drop_for_all_tab CURSOR IS SELECT drop_stmt FROM table_indexes;

    cur_create_for_specific_tab CURSOR (tab_name varchar) IS SELECT recreate_stmt FROM table_indexes WHERE table_indexes.table_name = table_name_to_drop;
    cur_create_for_all_tab CURSOR IS SELECT recreate_stmt FROM table_indexes;

BEGIN

  IF upper(in_operation) = 'DROP' THEN
    IF upper(in_table_name_or_all_option) ='ALL' THEN
      FOR table_record IN cur_drop_for_all_tab LOOP
        EXECUTE table_record.drop_stmt;
      END LOOP;

    ELSE
      FOR table_record IN cur_drop_for_specific_tab(in_table_name_or_all_option) LOOP
        EXECUTE table_record.drop_stmt;
      END LOOP;
    END IF;
  ELSIF upper(in_operation) = 'CREATE' THEN
    IF upper(in_table_name_or_all_option) ='ALL' THEN
      FOR table_record IN cur_create_for_all_tab LOOP
        EXECUTE table_record.recreate_stmt;
      END LOOP;
    ELSE
      FOR table_record IN cur_create_for_specific_tab(in_table_name_or_all_option) LOOP
        EXECUTE table_record.recreate_stmt;
      END LOOP;
    END IF;
  END IF;
    RETURN 1;
END;
$$ LANGUAGE plpgsql;

Refactored Function (s)

CREATE OR REPLACE FUNCTION execute_recreate_stmt_from_records(input_cursor refcursor) RETURNS integer AS $$
  BEGIN
    FOR table_record IN input_cursor LOOP
      EXECUTE table_record.recreate_stmt;
    END LOOP;
    RETURN 1;
  END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION execute_drop_stmt_from_records(input_cursor refcursor) RETURNS integer AS $$
  BEGIN
    FOR table_record IN input_cursor LOOP
      EXECUTE table_record.drop_stmt;
    END LOOP;
    RETURN 1;
  END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION drop_indexes_and_constraints(table_name_to_drop varchar) RETURNS integer AS $$
  DECLARE
    indexes_and_constraints CURSOR IS SELECT drop_stmt FROM table_indexes WHERE table_indexes.table_name = table_name_to_drop;
  SELECT execute_drop_stmt_from_records(indexes_and_constraints);
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION drop_all_indexes_and_constraints() RETURNS integer AS $$
  DECLARE
    indexes_and_constraints CURSOR IS SELECT drop_stmt FROM table_indexes;
  SELECT execute_drop_stmt_from_records(indexes_and_constraints);
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION recreate_indexes_and_constraints(table_name_to_recreate varchar) RETURNS integer AS $$
  DECLARE
    indexes_and_constraints CURSOR IS SELECT recreate_stmt FROM table_indexes WHERE table_indexes.table_name = table_name_to_recreate;
  SELECT execute_recreate_stmt_from_records(indexes_and_constraints);
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION recreate_all_indexes_and_constraints() RETURNS integer AS $$
  DECLARE
    indexes_and_constraints CURSOR IS SELECT recreate_stmt FROM table_indexes;
  SELECT execute_recreate_stmt_from_records(indexes_and_constraints);
$$ LANGUAGE plpgsql;

I believe that the main problem with my refactor is that auxiliary functions execute_recreate_stmt_from_recordsand execute_drop_stmt_from_recordstoo effective to be publicly available, especially since Heroku (which places this DB) allows only one user database. Of course, if there are other problems with the above refactor, feel free to specify them.

Answer 1

, "private" , . , "private".

, .

:

CREATE USER app_user;
CREATE USER private_user;

GRANT ALL ON DATABASE my_database TO app_user;
GRANT CONNECT, CREATE ON DATABASE my_database TO private_user;

-- With private_user:
CREATE SCHEMA private;

CREATE OR REPLACE FUNCTION private.test_func1()
    RETURNS integer AS
$BODY$
BEGIN
    RETURN 123;
END
$BODY$
    LANGUAGE plpgsql STABLE
    COST 100;

CREATE OR REPLACE FUNCTION public.my_function_1()
    RETURNS integer AS
$BODY$
DECLARE

BEGIN
    RETURN private.test_func1();
END
$BODY$
    LANGUAGE plpgsql VOLATILE SECURITY DEFINER
    COST 100;

-- With app_user:
SELECT private.test_func1();  -- ERROR: permission denied for schema private
SELECT my_function_1();       -- Returns 123