{"id":269,"date":"2017-03-30T10:18:33","date_gmt":"2017-03-30T09:18:33","guid":{"rendered":"http:\/\/eppesuigoccas.homedns.org\/wordpress\/?p=269"},"modified":"2017-03-30T11:56:53","modified_gmt":"2017-03-30T10:56:53","slug":"usare-delle-funzioni-che-restituiscono-result-set-nella-parte-select-di-costrutti-sql-in-postgresql","status":"publish","type":"post","link":"https:\/\/blog.sguazz.it\/index.php\/archives\/269","title":{"rendered":"Usare delle funzioni che restituiscono result set nella parte SELECT di costrutti SQL in PostgreSQL"},"content":{"rendered":"

[English page]<\/a>
\nDi recente ho dovuto scrivere una query che trasformasse ogni record di una tabella, in una serie di record. In particolare, da una tabella che ha due colonne (la prima \u00e8 la chiave, la seconda \u00e8 un elenco di targhe) ho dovuto estrarre un result set che avesse due colonne: la chiave e una sola targa. Per quei record che hanno pi\u00f9 targhe, si doveva arrivare a duplicare il record in modo da avere la coppia chiave\/targa per ciascuna delle targhe nell’elenco relativo.<\/p>\n

Altrimenti detto:<\/p>\n

postgres=# create temporary table t (chiave varchar primary key, targhe varchar);\r\npostgres=# insert into t values ('000000','AA888BB CC777DD GG333JJ'), ('111111','ZZ888KK');\r\nINSERT 0 2\r\npostgres=# select * from t;\r\n chiave |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 targhe\r\n--------+-------------------------\r\n 000000 | AA888BB CC777DD GG333JJ\r\n 111111 | ZZ888KK<\/pre>\n
quello che volevo era:<\/p>\n
 chiave |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 targa\r\n--------+-------------------------\r\n 000000 | AA888BB\r\n 000000 | CC777DD\r\n 000000 | GG333JJ\r\n 111111 | ZZ888KK<\/pre>\n
la soluzione che avevo trovato era la seguente:<\/p>\n
postgres=# select chiave,\r\n    unnest(regexp_split_to_array(targhe, E'\\\\s+')) AS targa from t;\r\n chiave |\u00a0 targa\r\n--------+---------\r\n 000000 | AA888BB\r\n 000000 | CC777DD\r\n 000000 | GG333JJ\r\n 111111 | ZZ888KK<\/pre>\n
Cosa fa la query che ho scritto? Primo, converte in array la colonna targhe usando il separatore spazio, secondo converte l’array in una relazione di vari record.<\/p>\n
Ma questa soluzione non mi piaceva, e neppure la capivo granch\u00e9. Mi sono quindi fatto alcune domande:<\/p>\n
    \n
  1. come mai posso mettere nella parte SELECT (e non nella FROM) una relazione? Difatti quando studi l’SQL impari che le relazioni (tabelle, viste, eccetera) vanno nella parte FROM che si usa proprio per dire da dove prendere i dati, mentre nella parte SELECT inserisci cosa visualizzare.<\/li>\n
  2. come decide, postgresql, di fare un prodotto cartesiano tra il primo elemento (singolo campo “chiave”) e il secondo (relazione “targa”)?<\/li>\n
  3. come fa postgresql a definire questa seconda relazione che non \u00e8 costante, ma dipende dal primo campo del record corrente? Vale a dire che per ogni “chiave” c’\u00e8 una relazione “targa” diversa. E poi, se veramente viene fatto il prodotto cartesiano, dovrei poter aggiungere un secondo unnest e produrre 4×3 record. Proviamo:\n
    postgres=# select chiave,\r\nunnest(regexp_split_to_array(targhe, E'\\\\s+')) AS targa1,\r\nunnest(regexp_split_to_array(targhe, E'\\\\s+')) AS targa2 from t;\r\n chiave | targa1\u00a0 | targa2\r\n--------+---------+---------\r\n 000000 | AA888BB | AA888BB\r\n 000000 | CC777DD | CC777DD\r\n 000000 | GG333JJ | GG333JJ\r\n 111111 | ZZ888KK | ZZ888KK<\/pre>\n<\/li>\n
  4. come mai non ha fatto un ulteriore prodotto? Pu\u00f2 avere a che fare con il concetto di funzioni IMMUTABLE alla quale forse unnest appartiene? (Mi pare di ricordare che si chiamino IMMUTABLE le funzioni che a fronte dello stesso input, danno lo stesso output. In questo caso postgresql potrebbe non richiamare nuovamente la unnest perch\u00e9 tanto conosce gi\u00e0 il risultato. Ma perch\u00e9 non fa un ulteriore prodotto?)controprova, faccio l’unnest con un altro array:\n
    postgres=# select chiave,\r\n    unnest(regexp_split_to_array(targhe, E'\\\\s+')) AS targa1,\r\n    unnest('{1,2}'::int[]) AS array2 from t;\r\n chiave | targa1\u00a0 | array2\r\n--------+---------+--------\r\n 000000 | AA888BB |\u00a0\u00a0\u00a0\u00a0\u00a0 1\r\n 000000 | CC777DD |\u00a0\u00a0\u00a0\u00a0\u00a0 2\r\n 000000 | GG333JJ |\u00a0\u00a0\u00a0\u00a0\u00a0 1\r\n 000000 | AA888BB |\u00a0\u00a0\u00a0\u00a0\u00a0 2\r\n 000000 | CC777DD |\u00a0\u00a0\u00a0\u00a0\u00a0 1\r\n 000000 | GG333JJ |\u00a0\u00a0\u00a0\u00a0\u00a0 2\r\n 111111 | ZZ888KK |\u00a0\u00a0\u00a0\u00a0\u00a0 1\r\n 111111 | ZZ888KK |\u00a0\u00a0\u00a0\u00a0\u00a0 2<\/pre>\n
    qui il prodotto l’ha fatto.<\/li>\n<\/ol>\n
    Con tanta confusione in testa, ho contattato alcune mailing list fino ad ottenere la risposta chiarificatrice di Tom Lane (qui<\/a>).<\/p>\n
    La risposta prevede vari punti:<\/p>\n
      \n
    1. la query migliore da fare prevede l’utilizzo del LATERAL e lo spostamento della funzione che restituisce l’array dalla parte SELECT alla parte FROM, nonch\u00e9 la sostituzione delle due chiamate unnest+regexp_split_to_array con una sola regexp_split_to_table:\n
      select chiave, targa\r\n    from t,\r\n        lateral regexp_split_to_table(targhe, E'\\\\s+') as targa;<\/pre>\n<\/li>\n
    2. il fatto che si possano mettere delle funzioni che restituiscono pi\u00f9 di un valore nella parte SELECT \u00e8 un dovuto alla derivazione di postgresql dal Berkeley QUEL. Di questa parte di codice Tom non \u00e8 particolarmente contento. Tra l’altro fa notare che in postgresql 10, il codice per la gestione di queste funzioni che restituiscono pi\u00f9 valori, \u00e8 stato notevolmente cambiato per centralizzarlo e isolarlo dal resto (vedi link nel suo email)<\/li>\n
    3. il prodotto cartesiano non viene fatto per ciascuna delle funzioni di questo tipo chiamate. Il numero finale di record viene in realt\u00e0 prodotto in base alla ricerca di un minimo comune multiplo della cardinalit\u00e0 dei result set delle varie funzioni. Questo spiega perch\u00e9 se ho due funzioni (nel mio caso, uguali) che restituiscono entrambe tre record, il mcm \u00e8 3, se ne ho una da 3 una da 2, il mcm \u00e8 6.<\/li>\n<\/ol>\n","protected":false},"excerpt":{"rendered":"
      [English page] Di recente ho dovuto scrivere una query che trasformasse ogni record di una tabella, in una serie di record. In particolare, da una tabella che ha due colonne (la prima \u00e8 la chiave, la seconda \u00e8 un elenco di targhe) ho dovuto estrarre un result set che avesse due colonne: la chiave e […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3,10],"tags":[13],"class_list":["post-269","post","type-post","status-publish","format-standard","hentry","category-computer","category-open-source","tag-itpug"],"_links":{"self":[{"href":"https:\/\/blog.sguazz.it\/index.php\/wp-json\/wp\/v2\/posts\/269","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blog.sguazz.it\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.sguazz.it\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.sguazz.it\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.sguazz.it\/index.php\/wp-json\/wp\/v2\/comments?post=269"}],"version-history":[{"count":4,"href":"https:\/\/blog.sguazz.it\/index.php\/wp-json\/wp\/v2\/posts\/269\/revisions"}],"predecessor-version":[{"id":279,"href":"https:\/\/blog.sguazz.it\/index.php\/wp-json\/wp\/v2\/posts\/269\/revisions\/279"}],"wp:attachment":[{"href":"https:\/\/blog.sguazz.it\/index.php\/wp-json\/wp\/v2\/media?parent=269"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.sguazz.it\/index.php\/wp-json\/wp\/v2\/categories?post=269"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.sguazz.it\/index.php\/wp-json\/wp\/v2\/tags?post=269"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}