Utilizzo pratico di regexp_split_to_table

Mi sono trovato nella condizione di dover fare la "traduzione" fra due sistemi di gestione permessi basati su tabelle PostgreSQL, ed ho trovato realmente utile la funzione interna regexp_split_to_table() embedded nel sistema.
La gestione di partenza prevedeva una tabella dove ogni entry conteneva l'utente alla quale si riferiva oltre ad una stringa CSV con i contesti ai quali l'utente aveva accesso, ossia qualcosa del tipo

SELECT id_utenti, cognome, nome, categorie_accessibili FROM utenti;
 id_utenti | cognome | nome |        categorie_accessibili        
-----------+---------+------+--------------------------------------
         1 | Ferrari | Luca | ambientazioni,bozzetti,pezzispeciali

In questo caso la colonna "categorie_accessibili" rappresenta il contesto al quale l'utente ha accesso. Ebbene mi e' stata richiesta la conversione di questo sistema di gestione dei permessi con uno piu' elaborato, dove oltre al contesto veniva specificato un sub-contesto e le relative azioni di lettura/scrittura con una tabella come la seguente

SELECT * FROM permessi;
 id_utenti | id_permessi |   categoria   |     tab      | lettura | scrittura
-----------+-------------+---------------+--------------+---------+-----------
         1 |           2 | ambientazioni | stato        |       0 |         0
         1 |           3 | ambientazioni | varie        |       0 |         0
         1 |           4 | ambientazioni | collegamenti |       0 |         0
         1 |           5 | presentazioni | generale     |       0 |         0
         1 |           6 | presentazioni | stato        |       0 |         0
         1 |           7 | presentazioni | varie        |       0 |         0
         1 |           8 | presentazioni | collegamenti |       0 |         0
         1 |          10 | pezzispeciali | stato        |       0 |         0
         1 |          11 | pezzispeciali | varie        |       0 |         0
         1 |          12 | pezzispeciali | collegamenti |       0 |         0
         1 |           1 | ambientazioni | generale     |       1 |         0
         1 |           9 | pezzispeciali | generale     |       1 |         0

In questo caso il contesto primario e' dato dalla colonna "categoria" mentre quello secondario dalla colonna "tab"; le colonne "lettura" e "scrittura" rappresentano invece i permessi di lettura e scrittura (come ovvio) in formato C-like (0 implica che non si ha il permesso, 1 che lo si ha).
Riassumendo quindi occorre convertire la colonna "utenti.categorie_accessibili" in una serie di entry nella tabella "permessi" splittando ogni valore contenuto in "categorie_accessibili" e mettendolo in "permessi.categoria". La logica vuole che ogni contesto/categoria presente in "categorie_accessibili" forzi un valore di "categoria"+"tab=generale"+"lettura" pari ad 1, lasciando gli altri valori immutati.

Ci sono delle precondizioni che hanno semplificato di molto il mio lavoro: tutti gli utenti presenti nella tabella "utenti" sono inseriti anche nella tabella "permessi", e per ciascuno di essi la lista dei permessi e' completa (ossia non ci sono nuove tuple da inserire, ma solo tuple da aggiornare). Inoltre la stringa "categorie_accessibili" e' ben formattata e non contiene spazi o altri caratteri che possano richiedere l'uso di espressioni regolari complicate.

Essendo troppo pigro per scrivere un programma di utilita' e non avendo voglia di scrivere una stored procedure per eseguire la trasformazione ho cominciato a riflettere su quale query mi avrebbe potuto aiutare. Inizialmente avevo pensato di costruire una query con un CASE che valutasse con la funzione position(..) la presenza di una "categoria" dentro alla stringa "categorie_accessibili" e producesse in uscita il valore 0/1 di conseguenza, ossia qualcosa come

SELECT u.id_utenti,u.cognome,u.nome, p.categoria,
       CASE WHEN position( p.categoria IN u.categorie_accessibili ) > 0
            THEN 1
            ELSE 0 END AS permesso
FROM utenti u JOIN permessi p ON u.id_utenti = p.id_utenti WHERE p.tab = 'generale';
che produce in uscita un risultato come segue

 id_utenti | cognome | nome |   categoria   | permesso
-----------+---------+------+---------------+----------
         1 | Ferrari | Luca | pezzispeciali |        1
         1 | Ferrari | Luca | ambientazioni |        1
         1 | Ferrari | Luca | presentazioni |        0

e quindi inserendo una simile query di selezione dentro ad uno statement di UPDATE si potrebbe ottenere il risultato voluto. Questa soluzione implica la lettura della tabella "permessi" per fare match su quella "utenti", e alla fine ho deciso di operare in modo leggermente differente. Grazie alla funzione regexp_split_to_table(..) e' possibile spezzare la stringa "categorie_accessibili" in una tabella da usare come subquery per la query di UPDATE, che quindi risulta:

UPDATE permessi p SET lettura = 1
WHERE tab = 'generale'
AND categoria IN
 ( SELECT regexp_split_to_table( u.categorie_accessibili, ',')
   FROM utenti u WHERE u.id_utenti = p.id_utenti
 );

L'idea e' quindi quella di procedere attraverso la tabella "permessi" aggiornado solo le tuple con sub-contesto "generale" che siano pero' presenti nella stringa "categorie_accessibili" della tabella "utenti".