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

发布时间：2020-12-13 15:53:03 所属栏目：百科来源：网络整理

导读：我有一个Postgres函数,有很多重复的逻辑.如果我在 Ruby中编写这个,我会将重复的逻辑提取到一些私有帮助器方法中.但在Postgres中似乎没有相当于“私人方法”的东西. 原始功能 CREATE OR REPLACE FUNCTION drop_create_idx_constraint(in_operation varchar,in

我有一个Postgres函数,有很多重复的逻辑.如果我在 Ruby中编写这个,我会将重复的逻辑提取到一些私有帮助器方法中.但在Postgres中似乎没有相当于“私人方法”的东西.

原始功能

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(托管此DB)只允许一个DB用户.当然,如果上面的重构有其他问题,请随意指出它们.

解决方法

您可以通过将“私有”过程移动到新模式来实现分离,从而限制对它的访问.然后使用SECURITY DEFINER允许调用“私有”功能.

但是,如果您的托管服务仅限于单个用户,则很难实现.

例：

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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!