Closed
Description
A refactor could be made by using table functions returning statements per relations, in that way, it could be easier to separate looping over staging tables and statement execution like I suggested in #24.
Example with db_indexes_statements table function:
CREATE FUNCTION db_indexes_statements(
plugin name,
pgstage_schema name DEFAULT NAME 'pgsql_stage'
) RETURNS TABLE (statement text)
LANGUAGE plpgsql VOLATILE CALLED ON NULL INPUT SET search_path = pg_catalog AS
$$DECLARE
BEGIN
/* loop through all index columns */
FOR <needed variables> IN <index columns query>
LOOP
stmt := format('CREATE INDEX') -- put any variables into this dynamic statement
-- do more logic...
return next;
END LOOP;
END; $$;CREATE FUNCTION db_migrate_indexes(
plugin name,
pgstage_schema name DEFAULT NAME 'pgsql_stage'
) RETURNS integer
LANGUAGE plpgsql VOLATILE CALLED ON NULL INPUT SET search_path = pg_catalog AS
$$DECLARE
rc integer := 0;
BEGIN
FOR stmt IN SELECT statement
FROM db_indexes_statements(plugin => 'mssql_server', pgstage_schema => 'pgsql_stage')
LOOP
EXECUTE stmt;
EXCEPTION
WHEN others THEN
-- printing exception detail
-- inserting exception into migrate_log table
END LOOP;
END; $$;With this uncorrelated system, it becomes possible to perform migration by external tools without calling neither db_migrate nor any db_migrate_* methods.
External tools in any language may consume db_migrator API (staging tables and statement table-functions as proposed) to execute statements and catch exceptions. From my point of view, it will provide a better way to speed-up migration with multiple processes.