Differenza tra linguaggio sql e linguaggio plpgsql nelle funzioni di PostgreSQL

Sono molto nuovo nello sviluppo del database quindi ho qualche dubbio sul mio esempio seguente:

Funzione f1 () – lingua sql

create or replace function f1(istr varchar) returns text as $$ select 'hello! '::varchar || istr; $$ language sql; 

Funzione f2 () – linguaggio plpgsql

  create or replace function f2(istr varchar) returns text as $$ begin select 'hello! '::varchar || istr; end; $$ language plpgsql; 

Un link utile o risposte riguardanti le funzioni saranno molto apprezzate.

Funzioni SQL

sono la scelta migliore:

  • Per semplici query scalari . Non c’è molto da pianificare, meglio risparmiare eventuali spese generali.

  • Per chiamate singole per sessione . Nulla da guadagnare dalla cache di pianificazione e dalle dichiarazioni preparate che PL / pgSQL ha da offrire. Vedi sotto.

  • Se vengono generalmente chiamati nel contesto di query più grandi e sono abbastanza semplici da essere sottolineati .

  • Per mancanza di esperienza con qualsiasi linguaggio procedurale come PL / pgSQL. Molti conoscono bene SQL e questo è tutto ciò che serve per le funzioni SQL. Pochi possono dire lo stesso su PL / pgSQL.

  • Un codice un po ‘più breve. Nessun blocco in testa.

Funzioni PL / pgSQL

sono la scelta migliore:

  • Quando hai bisogno di elementi procedurali o variabili che non sono disponibili nelle funzioni SQL, ovviamente.

  • Per qualsiasi tipo di SQL dinamico , in cui si creano ed EXECUTE istruzioni EXECUTE modo dinamico. È necessaria particolare cura per evitare l’iniezione SQL. Più dettagli:

    • Funzioni Postgres vs query preparate
  • Quando si dispone di calcoli che possono essere riutilizzati in più punti e un CTE non può essere allungato per lo scopo. In una funzione SQL non si hanno variabili e si è costretti a calcolare ripetutamente o scrivere su una tabella. Questa risposta correlata su dba.SE ha esempi di codice affiancati per risolvere lo stesso problema utilizzando una funzione SQL / una funzione plpgsql / una query con CTE:

    • Come passare un parametro in una funzione

    Le assegnazioni sono alquanto più costose rispetto ad altre lingue procedurali. Adatta uno stile di programmazione che non usa più incarichi del necessario.

  • Quando una funzione non può essere inline e viene chiamata più volte. A differenza delle funzioni SQL, i piani di query possono essere memorizzati nella cache per tutte le istruzioni SQL all’interno di funzioni PL / pgSQL ; vengono trattate come istruzioni preparate , il piano viene memorizzato nella cache per chiamate ripetute all’interno della stessa sessione (se Postgres si aspetta che il piano (generico) memorizzato nella cache esegua meglio della riprogettazione ogni volta). Ecco perché le funzioni PL / pgSQL sono in genere più veloci il primo paio di chiamate in questi casi.

    Ecco una discussione su pgsql-performance che discute alcuni di questi elementi:
    Ri: le funzioni di pl / pgsql superano quelle di sql?

  • Quando hai bisogno di intrappolare gli errori .

  • Per le procedure di trigger (che sono anche solo funzioni).

  • Quando si includono istruzioni DDL che modificano oggetti o alterano i cataloghi di sistema in qualsiasi modo rilevanti per i comandi successivi, poiché tutte le istruzioni nelle funzioni SQL vengono analizzate contemporaneamente mentre le funzioni PL / pgSQL pianificano ed eseguono ogni istruzione in modo sequenziale (come un’istruzione preparata). Vedere:

    • Perché le funzioni PL / pgSQL possono avere effetti collaterali, mentre le funzioni SQL non possono?

Considera anche:

  • Prestazioni di stored procedure PostgreSQL

Per completezza: per tornare effettivamente da una funzione PL / pgSQL, è ansible scrivere:

 CREATE FUNCTION f2(istr varchar) RETURNS text AS $func$ BEGIN RETURN 'hello! '; -- defaults to type text anyway END $func$ LANGUAGE plpgsql; 

Ci sono altri modi:

  • Posso fare in modo che una funzione plpgsql restituisca un intero senza usare una variabile?
  • Il manuale su “Ritorno da una funzione”

PL / PgSQL è un linguaggio procedurale specifico per PostgreSQL basato su SQL . Ha cicli, variabili, gestione di errori / eccezioni, ecc. Non tutti gli SQL sono validi PL / PgSQL – come hai scoperto, ad esempio, non puoi usare SELECT senza INTO o RETURN QUERY . PL / PgSQL può anche essere utilizzato in blocchi DO per procedure one-shot.

sql funzioni sql possono utilizzare solo SQL puro, ma sono spesso più efficienti e sono più semplici da scrivere perché non è necessario un BEGIN ... END; blocco, ecc. Le funzioni SQL possono essere inline, il che non è vero per PL / PgSQL.

Le persone spesso usano PL / PgSQL dove il semplice SQL sarebbe sufficiente, perché sono abituati a pensare proceduralmente. Nella maggior parte dei casi quando pensi di aver bisogno di PL / PgSQL probabilmente non lo fai. Le CTE ricorsive, le interrogazioni laterali, ecc. Generalmente soddisfano la maggior parte delle esigenze.

Per maggiori informazioni … vedi il manuale.