From 12633a9644ea494581007b670fec6310b9f8fb29 Mon Sep 17 00:00:00 2001 From: Jason Etheridge Date: Tue, 4 Sep 2018 01:46:04 -0400 Subject: [PATCH 1/1] migration_tools.handle_link3_concat_skip_null Signed-off-by: Jason Etheridge --- sql/base/base.sql | 44 +++++++++++++++++++++++++++++++++++++++++++- 1 files changed, 43 insertions(+), 1 deletions(-) diff --git a/sql/base/base.sql b/sql/base/base.sql index 2907444..6e5dfdc 100644 --- a/sql/base/base.sql +++ b/sql/base/base.sql @@ -3977,7 +3977,49 @@ CREATE OR REPLACE FUNCTION migration_tools.handle_link3_skip_null (TEXT,TEXT,TEX || ' SET ' || quote_ident(column_x) || ' = ' || 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 NULL'; + || ' AND NULLIF(a.' || quote_ident(column_w) || ','''') IS NOT NULL'; + + 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; + 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) || ' = CONCAT_WS('' ; '',' || quote_ident(column_x) || ',' || 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'; END; $$ LANGUAGE PLPGSQL STRICT VOLATILE; -- 1.7.2.5