PostgreSQL, split d’une colonne avec array_to_string et regexp_matches

Il m’a fallu aujourd’hui exploiter une table d’historique dont les données étaient stockées ainsi :

create table history (
    id_history serial primary key,
    actions text
);

avec actions de cette forme

old_firstname=john&new_firstname=bill&old_lastname=doe&new_lastname=dooh

ce qui m’aurait arrangé ça aurait été d’avoir un historique du type

create table history (
    id_history serial primary key,
    field text,
    old_value text,
    new_value text
);

mais je me contenterais d’une vue pour obtenir le même résultat. Cette vue peut être construite à l’aide des fonctions array_to_string et regexp_matches

create view nice_history as 
select  
    n.id_history, 
    n.field,
    o.old_value,
    n.new_value                          
from 
    (select
        id_history,                
        array_to_string(regexp_matches(new, 'new_([^=]+)=.+'), '') as field,
        array_to_string(regexp_matches(new, 'new_[^=]+=(.+)'), '') as new_value
    from 
        (select
            h.id_history,
            array_to_string(regexp_matches(h.actions,'(new_[^=]+=[^&]+)','g'),'') as new
        from history h) as q
    ) as n                                                           
left outer join 
    (select
        id_history,                                                          
        array_to_string(regexp_matches(old, 'old_([^=]+)=.+'), '') as field,
        array_to_string(regexp_matches(old, 'old_[^=]+=(.+)'), '') as old_value
    from 
        (select
            h.id_history,
            array_to_string(regexp_matches(h.actions,'(old_[^=]+=[^&]+)','g'),'') as old
        from history h) as q2
    ) as o on (o.field=n.field and o.id_history=n.id_history);

on peut maintenant exploiter simplement l’historique

guil=# select * from history;
 id_history |                                 actions                                  
------------+--------------------------------------------------------------------------
          1 | old_firstname=john&new_firstname=bill&old_lastname=doe&new_lastname=dooh
          2 | new_status=3
(2 rows)

guil=# select * from nice_history ;
 id_history |   field   | old_value | new_value 
------------+-----------+-----------+-----------
          1 | firstname | john      | bill
          1 | lastname  | doe       | dooh
          2 | status    |           | 3
(3 rows)

About the Author: Guillaume Luchet

Guillaume Luchet est Directeur de la R&D et Lead Développeur chez Bilendi Technology, entrepreneur et développeur freelance.