Come evitare la valutazione di più funzioni con la syntax (func ()). * In una query SQL?

Contesto

Quando una funzione restituisce un tipo di SETOF composite-type TABLE o SETOF composite-type , come questa funzione di esempio:

 CREATE FUNCTION func(n int) returns table(i int, j bigint) as $$ BEGIN RETURN QUERY select 1,n::bigint union all select 2,n*n::bigint union all select 3,n*n*n::bigint; END $$ language plpgsql; 

i risultati sono accessibili con vari metodi:

1) select * from func(3) produrrà queste colonne di output:

  io |  j 
 --- + ---
  1 |  3
  2 |  9
  3 |  27

2) select func(3) produrrà solo una colonna di uscita di tipo ROW.

  func  
 -------
  (1,3)
  (2,9)
  (3,27)

3) select (func(3)).* Produrrà come # 1:

  io |  j 
 --- + ---
  1 |  3
  2 |  9
  3 |  27

Quando l’argomento della funzione proviene da una tabella o una sottoquery, la syntax n. 3 è l’unica ansible, come in:

 select N, (func(N)).* from (select 2 as N union select 3 as N) s; 

o come in questa risposta correlata. Se avessimo LATERAL JOIN , potremmo usarlo, ma fino a che PostgreSQL 9.3 non sarà supportato, e le versioni precedenti saranno comunque utilizzate per anni.

Problema

Ora il problema con la syntax # 3 è che la funzione viene chiamata tante volte quante sono le colonne nel risultato. Non c’è una ragione apparente per quello, ma succede. Possiamo vederlo nella versione 9.2 aggiungendo un RAISE NOTICE 'called for %', n nella funzione. Con la query sopra, emette:

 AVVISO: richiesto per 2
 AVVISO: richiesto per 2
 AVVISO: richiesto per 3
 AVVISO: richiesto per 3

Ora se la funzione è cambiata per restituire 4 colonne, in questo modo:

 CREATE FUNCTION func(n int) returns table(i int, j bigint,k int, l int) as $$ BEGIN raise notice 'called for %', n; RETURN QUERY select 1,n::bigint,1,1 union all select 2,n*n::bigint,1,1 union all select 3,n*n*n::bigint,1,1; END $$ language plpgsql stable; 

quindi gli stessi risultati della query:

 AVVISO: richiesto per 2
 AVVISO: richiesto per 2
 AVVISO: richiesto per 2
 AVVISO: richiesto per 2
 AVVISO: richiesto per 3
 AVVISO: richiesto per 3
 AVVISO: richiesto per 3
 AVVISO: richiesto per 3

Sono state necessarie 2 chiamate di funzione, 8 sono state effettivamente realizzate. Il rapporto è il numero di colonne di output.

Con la syntax n. 2 che produce lo stesso risultato ad eccezione del layout delle colonne di output, queste chiamate multiple non avvengono:

 select N,func(N) from (select 2 as N union select 3 as N) s; 

dà:

 AVVISO: richiesto per 2
 AVVISO: richiesto per 3

seguito dalle 6 righe risultanti:

  n |  func    
 --- + ------------
  2 |  (1,2,1,1)
  2 |  (2,4,1,1)
  2 |  (3,8,1,1)
  3 |  (1,3,1,1)
  3 |  (2,9,1,1)
  3 |  (3,27,1,1)

Domande

C’è una syntax o un costrutto con 9.2 che raggiungerebbe il risultato atteso facendo solo le chiamate di funzione richieste minime?

Domanda bonus: perché le valutazioni multiple avvengono?

Puoi racchiuderlo in una sottoquery ma non è sicuro sicuro senza l’hack OFFSET 0 . In 9.3, usare LATERAL . Il problema è causato dal parser in modo efficace la macro-espansione * in un elenco di colonne.

Soluzione

Dove:

 SELECT (my_func(x)).* FROM some_table; 

valuterà my_func n volte per n colonne risultato dalla funzione, questa formulazione:

 SELECT (mf).* FROM ( SELECT my_func(x) AS mf FROM some_table ) sub; 

generalmente non lo faranno, e tende a non aggiungere una scansione aggiuntiva in fase di runtime. Per garantire che non venga eseguita la valutazione multipla, è ansible utilizzare l’ OFFSET 0 hack o abusare dell’errore di PostgreSQL per ottimizzare i confini CTE:

 SELECT (mf).* FROM ( SELECT my_func(x) AS mf FROM some_table OFFSET 0 ) sub; 

o:

 WITH tmp(mf) AS ( SELECT my_func(x) FROM some_table ) SELECT (mf).* FROM tmp; 

In PostgreSQL 9.3 puoi usare LATERAL per ottenere un comportamento più sicuro:

 SELECT mf.* FROM some_table LEFT JOIN LATERAL my_func(some_table.x) AS mf ON true; 

LEFT JOIN LATERAL ... ON true conserva tutte le righe come la query originale, anche se la funzione call non restituisce alcuna riga.

dimostrazione

Creare una funzione che non sia inlineabile come dimostrazione:

 CREATE OR REPLACE FUNCTION my_func(integer) RETURNS TABLE(a integer, b integer, c integer) AS $$ BEGIN RAISE NOTICE 'my_func(%)',$1; RETURN QUERY SELECT $1, $1, $1; END; $$ LANGUAGE plpgsql; 

e una tabella di dati fittizi:

 CREATE TABLE some_table AS SELECT x FROM generate_series(1,10) x; 

quindi prova le versioni precedenti. Vedrai che il primo solleva tre notifiche per invocazione; il secondo ne solleva uno solo.

Perché?

Buona domanda. È orribile.

Sembra:

 (func(x)).* 

è espanso come:

 (my_func(x)).i, (func(x)).j, (func(x)).k, (func(x)).l 

in fase di parsing, secondo uno sguardo a debug_print_parse , debug_print_rewritten e debug_print_plan . L’albero di analisi (tagliato) si presenta così:

  :targetList ( {TARGETENTRY :expr {FIELDSELECT :arg {FUNCEXPR :funcid 57168 ... } :fieldnum 1 :resulttype 23 :resulttypmod -1 :resultcollid 0 } :resno 1 :resname i ... } {TARGETENTRY :expr {FIELDSELECT :arg {FUNCEXPR :funcid 57168 ... } :fieldnum 2 :resulttype 20 :resulttypmod -1 :resultcollid 0 } :resno 2 :resname j ... } {TARGETENTRY :expr {FIELDSELECT :arg {FUNCEXPR :funcid 57168 ... } :fieldnum 3 :... } :resno 3 :resname k ... } {TARGETENTRY :expr {FIELDSELECT :arg {FUNCEXPR :funcid 57168 ... } :fieldnum 4 ... } :resno 4 :resname l ... } ) 

Quindi, in pratica, stiamo usando un hacker del parser per espandere i caratteri jolly clonando i nodes.