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;
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;
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;
END;
$$ LANGUAGE PLPGSQL STRICT VOLATILE;
+
+DROP FUNCTION IF EXISTS migration_tools.btrim_lcolumns(TEXT,TEXT);
+CREATE OR REPLACE FUNCTION migration_tools.btrim_lcolumns(s_name TEXT, t_name TEXT) RETURNS BOOLEAN
+ LANGUAGE plpgsql
+AS $function$
+DECLARE
+ c_name TEXT;
+BEGIN
+
+ FOR c_name IN SELECT column_name FROM information_schema.columns WHERE
+ table_name = t_name
+ AND table_schema = s_name
+ AND (data_type='text' OR data_type='character varying')
+ AND column_name like 'l_%'
+ LOOP
+ EXECUTE FORMAT('UPDATE ' || s_name || '.' || t_name || ' SET ' || c_name || ' = BTRIM(' || c_name || ')');
+ END LOOP;
+
+ RETURN TRUE;
+END
+$function$;
+