postgresql - 我应该如何提取 Postgres 函数中的重复逻辑？

Question

我有一个带有很多重复逻辑的 Postgres 函数。如果我用 Ruby 来写这个，我会把重复的逻辑提取到一些私有(private)的辅助方法中。但是在 Postgres 中似乎没有对应的“私有(private)方法”。

原始函数

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;

重构函数

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;

我相信我的重构的潜在问题是辅助函数 execute_recreate_stmt_from_records 和 execute_drop_stmt_from_records 太强大以至于无法公开访问，特别是因为 Heroku(托管这个数据库)只允许一个数据库用户。当然，如果上述重构还有其他问题，欢迎指出。

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