From: Jason Etheridge Date: Tue, 4 Sep 2018 05:53:28 +0000 (-0400) Subject: migration_tools.handle_link3_skip_true, and remove potential for ambiguous columns... X-Git-Url: http://git.equinoxoli.org/?p=migration-tools.git;a=commitdiff_plain;h=e6b9f09ec9e3591e1468615fad51ab7a99ed2fc1 migration_tools.handle_link3_skip_true, and remove potential for ambiguous columns for all handle_link* Signed-off-by: Jason Etheridge --- diff --git a/sql/base/base.sql b/sql/base/base.sql index 6e5dfdc..0aa371e 100644 --- a/sql/base/base.sql +++ b/sql/base/base.sql @@ -3826,12 +3826,12 @@ CREATE OR REPLACE FUNCTION migration_tools.handle_link (TEXT,TEXT,TEXT,TEXT,TEXT IF btrim_desired THEN EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b' - || ' SET ' || quote_ident(column_x) || ' = id FROM ' || quote_ident(table_a) || ' a' + || ' SET ' || quote_ident(column_x) || ' = a.id FROM ' || quote_ident(table_a) || ' a' || ' WHERE BTRIM(a.' || quote_ident(column_a) || ') = BTRIM(b.' || quote_ident(column_b) || ')'; ELSE EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b' - || ' SET ' || quote_ident(column_x) || ' = id FROM ' || quote_ident(table_a) || ' a' + || ' SET ' || quote_ident(column_x) || ' = a.id FROM ' || quote_ident(table_a) || ' a' || ' WHERE a.' || quote_ident(column_a) || ' = b.' || quote_ident(column_b); END IF; @@ -3884,12 +3884,12 @@ CREATE OR REPLACE FUNCTION migration_tools.handle_link2 (TEXT,TEXT,TEXT,TEXT,TEX IF btrim_desired THEN EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b' - || ' SET ' || quote_ident(column_x) || ' = ' || quote_ident(column_w) || ' FROM ' || quote_ident(table_a) || ' a' + || ' SET ' || quote_ident(column_x) || ' = a.' || quote_ident(column_w) || ' FROM ' || quote_ident(table_a) || ' a' || ' WHERE BTRIM(a.' || quote_ident(column_a) || ') = BTRIM(b.' || quote_ident(column_b) || ')'; ELSE EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b' - || ' SET ' || quote_ident(column_x) || ' = ' || quote_ident(column_w) || ' FROM ' || quote_ident(table_a) || ' a' + || ' SET ' || quote_ident(column_x) || ' = a.' || quote_ident(column_w) || ' FROM ' || quote_ident(table_a) || ' a' || ' WHERE a.' || quote_ident(column_a) || ' = b.' || quote_ident(column_b); END IF; @@ -3933,7 +3933,7 @@ CREATE OR REPLACE FUNCTION migration_tools.handle_link3 (TEXT,TEXT,TEXT,TEXT,TEX END IF; EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b' - || ' SET ' || quote_ident(column_x) || ' = ' || quote_ident(column_w) || ' FROM ' || quote_ident(table_a) || ' a' + || ' SET ' || quote_ident(column_x) || ' = a.' || quote_ident(column_w) || ' FROM ' || quote_ident(table_a) || ' a' || ' WHERE a.' || quote_ident(column_a) || ' = b.' || quote_ident(column_b); @@ -3974,7 +3974,7 @@ CREATE OR REPLACE FUNCTION migration_tools.handle_link3_skip_null (TEXT,TEXT,TEX END IF; EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b' - || ' SET ' || quote_ident(column_x) || ' = ' || quote_ident(column_w) || ' FROM ' || quote_ident(table_a) || ' a' + || ' SET ' || quote_ident(column_x) || ' = a.' || quote_ident(column_w) || ' FROM ' || quote_ident(table_a) || ' a' || ' WHERE a.' || quote_ident(column_a) || ' = b.' || quote_ident(column_b) || ' AND NULLIF(a.' || quote_ident(column_w) || ','''') IS NOT NULL'; @@ -3982,6 +3982,48 @@ CREATE OR REPLACE FUNCTION migration_tools.handle_link3_skip_null (TEXT,TEXT,TEX END; $$ LANGUAGE PLPGSQL STRICT VOLATILE; +CREATE OR REPLACE FUNCTION migration_tools.handle_link3_skip_true (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$ + DECLARE + table_schema ALIAS FOR $1; + table_a ALIAS FOR $2; + column_a ALIAS FOR $3; + table_b ALIAS FOR $4; + column_b ALIAS FOR $5; + column_w ALIAS FOR $6; + column_x ALIAS FOR $7; + proceed BOOLEAN; + BEGIN + EXECUTE 'SELECT EXISTS ( + SELECT 1 + FROM information_schema.columns + WHERE table_schema = $1 + AND table_name = $2 + and column_name = $3 + )' INTO proceed USING table_schema, table_a, column_a; + IF NOT proceed THEN + RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a; + END IF; + + EXECUTE 'SELECT EXISTS ( + SELECT 1 + FROM information_schema.columns + WHERE table_schema = $1 + AND table_name = $2 + and column_name = $3 + )' INTO proceed USING table_schema, table_b, column_b; + IF NOT proceed THEN + RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b; + END IF; + + EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b' + || ' SET ' || quote_ident(column_x) || ' = a.' || quote_ident(column_w) || ' FROM ' || quote_ident(table_a) || ' a' + || ' WHERE a.' || quote_ident(column_a) + || ' = b.' || quote_ident(column_b) + || ' AND a.' || quote_ident(column_w) || ' IS NOT TRUE'; + + END; +$$ LANGUAGE PLPGSQL STRICT VOLATILE; + CREATE OR REPLACE FUNCTION migration_tools.handle_link3_concat_skip_null (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$ DECLARE table_schema ALIAS FOR $1; @@ -4016,7 +4058,7 @@ CREATE OR REPLACE FUNCTION migration_tools.handle_link3_concat_skip_null (TEXT,T END IF; EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b' - || ' SET ' || quote_ident(column_x) || ' = CONCAT_WS('' ; '',' || quote_ident(column_x) || ',' || quote_ident(column_w) || ') FROM ' || quote_ident(table_a) || ' a' + || ' SET ' || quote_ident(column_x) || ' = CONCAT_WS('' ; '',b.' || quote_ident(column_x) || ',a.' || quote_ident(column_w) || ') FROM ' || quote_ident(table_a) || ' a' || ' WHERE a.' || quote_ident(column_a) || ' = b.' || quote_ident(column_b) || ' AND NULLIF(a.' || quote_ident(column_w) || ','''') IS NOT NULL';