From ea3559a4f7ac5fefcc0e64b1e610b26004d75d9f Mon Sep 17 00:00:00 2001 From: Jason Etheridge Date: Tue, 17 Apr 2018 10:41:01 -0400 Subject: [PATCH] migration_tools.parse_out_address2 and some fixes to handle_*_barcode Signed-off-by: Jason Etheridge --- sql/base/base.sql | 24 ++++++++++++++++++++---- 1 files changed, 20 insertions(+), 4 deletions(-) diff --git a/sql/base/base.sql b/sql/base/base.sql index 8dd7f36..632d231 100644 --- a/sql/base/base.sql +++ b/sql/base/base.sql @@ -607,6 +607,22 @@ CREATE OR REPLACE FUNCTION migration_tools.parse_out_address (TEXT) RETURNS TEXT END; $$ LANGUAGE PLPGSQL STRICT VOLATILE; +CREATE OR REPLACE FUNCTION migration_tools.parse_out_address2 (TEXT) RETURNS TEXT AS $$ + my ($address) = @_; + + use Geo::StreetAddress::US; + + my $a = Geo::StreetAddress::US->parse_location($address); + + return [ + "$a->{number} $a->{prefix} $a->{street} $a->{type} $a->{suffix}" + ,"$a->{sec_unit_type} $a->{sec_unit_num}" + ,$a->{city} + ,$a->{state} + ,$a->{zip} + ]; +$$ LANGUAGE PLPERLU STABLE; + CREATE OR REPLACE FUNCTION migration_tools.rebarcode (o TEXT, t BIGINT) RETURNS TEXT AS $$ DECLARE n TEXT := o; @@ -3359,12 +3375,12 @@ CREATE OR REPLACE FUNCTION migration_tools.handle_item_barcode (TEXT,TEXT,TEXT,T IF btrim_desired THEN EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a' - || ' SET x_item = id FROM asset_copy_legacy b' + || ' SET x_item = b.id FROM asset_copy_legacy b' || ' WHERE BTRIM(a.' || quote_ident(foreign_column_name) || ') = BTRIM(b.' || quote_ident(main_column_name) || ')'; ELSE EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a' - || ' SET x_item = id FROM asset_copy_legacy b' + || ' SET x_item = b.id FROM asset_copy_legacy b' || ' WHERE a.' || quote_ident(foreign_column_name) || ' = b.' || quote_ident(main_column_name); END IF; @@ -3420,12 +3436,12 @@ CREATE OR REPLACE FUNCTION migration_tools.handle_user_barcode (TEXT,TEXT,TEXT,T IF btrim_desired THEN EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a' - || ' SET x_user = id FROM actor_usr_legacy b' + || ' SET x_user = b.id FROM actor_usr_legacy b' || ' WHERE BTRIM(a.' || quote_ident(foreign_column_name) || ') = BTRIM(b.' || quote_ident(main_column_name) || ')'; ELSE EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a' - || ' SET x_user = id FROM actor_usr_legacy b' + || ' SET x_user = b.id FROM actor_usr_legacy b' || ' WHERE a.' || quote_ident(foreign_column_name) || ' = b.' || quote_ident(main_column_name); END IF; -- 1.7.2.5