代码之家 › 专栏 › 技术社区 › Simon Lepkin

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

heroku-postgres encapsulation heroku stored-procedures postgresql

Simon Lepkin · 技术社区 · 8 年前

我有一个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(托管此数据库)只允许一个数据库用户。当然,如果上述重构还有其他问题,请随时指出。

1 回复 | 直到 8 年前

Boris Schegolev 8 年前

您可以通过将“私有”过程移动到新的模式中,限制对它的访问来实现分离。然后使用安全定义器允许调用“私有”函数。

尽管如此,如果您的托管服务仅限于一个用户,这将很难实现。

例子:

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