get_adr_id_b_pg_varchar__varchar_.txt

-- DROP FUNCTION schema_xyz.get_adr_id_b_pg(varchar, varchar);

CREATE OR REPLACE FUNCTION schema_xyz.get_adr_id_b_pg(address_full_in character varying, city_in character varying)
 RETURNS bigint
 LANGUAGE plpgsql
AS $function$

DECLARE
/*
Returns an adr_id value for an address based on the result_II table (which must contain address_full, city and adr_id).
*/
DECLARE

ergebnis bigint;

BEGIN
   

select /*+ parallel(auto) */ distinct(adr_id) into ergebnis
from schema_xyz.result_II a
join schema_xyz.result_I b
on a.address_full=b.address_full and coalesce(a.city,'0')=coalesce(b.city,'0')
where b.address_full_original=address_full_in and coalesce(b.city_original,'0')=coalesce(city_in,'0');

return ergebnis;

   
END;
$function$
;