PostgreSQL – How should I extract repetition logic in the Postgres 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;

 upper
BEGIN
 (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.d rop_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;< br /> 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_drop_st_index_drop;_rains(mt) execute
 SELECT_from and execute
 >$$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_drops(mt_from indexes_and_constraints);
$$LANGUAGE plpgsql;

C REATE 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;$$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_st indexes_and_constraints);
$$LANGUAGE plpgsql;

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

I have a Postgres function , There is a lot of repeated logic. If I write this in Ruby, I will extract the repeated logic into some private helper methods. But there seems to be no equivalent of “private methods” in Postgres. < p>

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;

 specific table_create_indexes varchar varchar) 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_o r_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; 
Refactoring function
 
 
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_cu rsor 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 .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 br /> indexes_and_constraint $$
 DECLAR E
 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;
$$LANGUAGE plpgsql;
 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);
psql; plpg > I believe the fundamental problem with my refactoring is that the helper functions execute_recreate_stmt_from_records and execute_drop_stmt_from_records are too powerful to be publicly accessible, especially because Heroku (hosting this DB) only allows one DB user. Of course, if there are other problems with the above refactoring, please Feel free to point them out.
You can achieve separation by moving the "private" process to the new mode, thereby restricting access to it. Then use SECURITY DEFINER to allow Call the "private" function. 
 
However, if your hosting service is limited to a single user, it is difficult to achieve.
 
Example:
 
< /p> 
CREATE USER app_user;
CREATE USER private_user;

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

-- With private_user:
CREATE SCHEMA private;

CREATE OR REPLACE FUNCTION private.test_func1()
 RETURNS integer AS< br />$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