I have 2 tables:

table A:

name - text
id   - text
id pattern: xx_yyyy_AAA (xx, yyyy contains digits, AAA contains letters)

table B:

name - text
id   - text
id pattern: yyyy_xx_zz_tt (xx, yyyy, zz, tt contains digits)

I want to run join between the 2 tables on id field and to use my own id field parser

i.e the are some rules that makes the 2 id's (2 different patterns) equal.

1. How can I do it ?
2. How can I run join between the 2 tables on id field and use my own id parser ?

CodePudding user response：

The correct solution is to normalize your data model and turn that single column into multiple columns using the correct data type.

Until then you can workaround this design error by splitting the values in the join condition:

select *
from table_a a 
  join table_b b 
    on split_part(a.id, '_', 1) = split_part(b.id, '_', 2)
   and split_part(a.id, '_', 2) = split_part(b.id, '_', 1)

If you want, you can turn that into a function:

create function match_badly_designed_ids(p_id_one text, p_id_two text)
  returns boolean
as
$$
select split_part(p_id_one, '_', 1) = split_part(p_id_two, '_', 2)
   and split_part(p_id_one, '_', 2) = split_part(p_id_one, '_', 1);
$$
language sql
immutable;

Then you can use it in a JOIN clause like this:

select *
from table_a a 
  join table_b b on match_badly_designed_ids(a.id, b.id);

CodePudding user response：

You can create a dedicated function to parse your table_A.id field and another one to parse your table_B.id field :

CREATE OR REPLACE FUNCTION table_A_id (IN id text, OUT x text, OUT y text, OUT A text)
RETURNS record LANGUAGE sql IMMUTABLE AS
$$
SELECT r[1] AS x, r[2] AS y, r[3] AS A
  FROM regexp_split_to_array(id, '_') AS r
$$ ;

CREATE OR REPLACE FUNCTION table_B_id (IN id text, OUT x text, OUT y text, OUT z text, OUT t text)
RETURNS record LANGUAGE sql IMMUTABLE AS
$$
SELECT r[1] AS y, r[2] AS x, r[3] AS z, r[4] AS t
  FROM regexp_split_to_array(id, '_') AS r
$$ ;

Then, you can join both tables using the functions table_A_id and table_B_id :

SELECT *   
  FROM table_A AS a
 INNER JOIN table_B AS b
    ON table_A_id(a.id).x = table_B_id(b.id).x
   AND table_A_id(a.id).y = table_B_id(b.id).y