Menu

CREATE FUNCTION PostgreSQL e stored procedure

CREATE FUNCTION definisce una funzione da chiamare dentro le query, scritta in SQL o in PL/pgSQL. CREATE PROCEDURE definisce una routine da eseguire con CALL, che può fare commit delle transazioni man mano.

Questa pagina include editor eseguibili: modifica, esegui e vedi subito l'output.

CREATE FUNCTION name(arg type) RETURNS type LANGUAGE sql AS $$ ... $$; definisce una funzione che le query possono chiamare come una funzione predefinita. Il corpo va tra i segni $$, in SQL semplice o in PL/pgSQL, il linguaggio procedurale di PostgreSQL. CREATE PROCEDURE (PostgreSQL 11+) definisce invece una routine che si esegue con CALL.

with_tax(50) usa l'aliquota predefinita e restituisce 60; with_tax(50, 0.1) restituisce 55. Gli argomenti si possono passare anche per nome: with_tax(50, rate => 0.1).

Scrivere la logica in PL/pgSQL

Usa LANGUAGE plpgsql quando la funzione ha bisogno di variabili, IF, cicli o gestione degli errori. Le variabili vanno in una sezione DECLARE e il codice va tra BEGIN ed END:

Un pacco da 4 kg costa la base di 5; un pacco espresso da 20 kg costa (5 + 10 * 0.5) * 2 = 20. Dentro PL/pgSQL, RAISE NOTICE 'value: %', cost; stampa un messaggio e RAISE EXCEPTION interrompe con un errore.

Quando la logica si può esprimere in SQL semplice, preferisci LANGUAGE sql: il planner può incorporare una funzione SQL semplice nella query che la chiama.

Restituire un insieme di righe

RETURNS TABLE (...) crea una funzione che restituisce righe. Chiamala in FROM come una tabella:

orders_of('ana') restituisce gli ordini 1 e 3. RETURNS SETOF orders restituisce invece righe intere della tabella orders al posto delle colonne elencate.

Da PostgreSQL 14, una funzione SQL può avere un corpo SQL standard invece di una stringa. PostgreSQL lo salva già analizzato e registra le tabelle e le colonne che usa, quindi non si possono eliminare finché la funzione esiste:

CREATE FUNCTION order_count(who text) RETURNS bigint
LANGUAGE sql STABLE
BEGIN ATOMIC
  SELECT count(*) FROM orders WHERE customer = who;
END;

Creare una stored procedure ed eseguirla con CALL

Una procedura non ha un tipo di ritorno; la esegui con CALL. Può modificare i dati e, quando la CALL non è dentro un blocco di transazione esplicito, può fare COMMIT a metà strada. È così che si elabora un lavoro grande a blocchi.

Il conto 1 finisce a 70 e il conto 2 a 80. Un trasferimento di 200 violerebbe il vincolo CHECK sul conto 1, e l'errore annullerebbe entrambi gli aggiornamenti.

FunzioneProcedura
Si esegue conSELECT f(...), ovunque in una querysolo CALL p(...)
Restituisceun valore, una riga o un insieme di righeniente (o parametri OUT/INOUT)
COMMIT / ROLLBACK all'internonosì, in PL/pgSQL, quando non è chiamata dentro un blocco di transazione
DasemprePostgreSQL 11

Indicare la volatilità: IMMUTABLE, STABLE, VOLATILE

L'etichetta di volatilità dice al planner cosa può dare per scontato su una funzione. Il valore predefinito è VOLATILE.

  • IMMUTABLE: il risultato dipende solo dagli argomenti e non cambia mai (with_tax). Obbligatoria per le funzioni usate in un'espressione di indice.
  • STABLE: il risultato può cambiare tra un'istruzione e l'altra ma non dentro la stessa, per esempio una funzione che legge tabelle (orders_of).
  • VOLATILE: tutto il resto, come le funzioni che usano random() o modificano i dati. PostgreSQL la richiama per ogni riga.

Un'etichetta sbagliata dà risultati sbagliati: una funzione IMMUTABLE che legge una tabella può restituire valori vecchi da un indice o da un piano in cache.

Sostituire, sovraccaricare ed eliminare una funzione

CREATE OR REPLACE FUNCTION cambia il corpo di una funzione esistente ma non può cambiarne il tipo di ritorno; per quello eliminala prima. PostgreSQL identifica una funzione dal nome e dai tipi degli argomenti, quindi f(int) e f(text) sono due funzioni diverse. Per eliminare una di due funzioni con lo stesso nome, indica i tipi degli argomenti:

CREATE OR REPLACE FUNCTION with_tax(price numeric, rate numeric DEFAULT 0.2) ...;
DROP FUNCTION with_tax(numeric, numeric);
DROP PROCEDURE transfer;

Il capitolo su PL/pgSQL documenta cicli, cursori, eccezioni e SQL dinamico con EXECUTE. Per eseguire una funzione in automatico quando cambiano delle righe, collegala a un trigger.

Domande frequenti

Che differenza c'è tra una funzione e una procedura in PostgreSQL?

Una funzione restituisce un valore e si chiama dentro una query (SELECT f(x)). Una procedura, aggiunta in PostgreSQL 11, si esegue da sola con CALL e può fare COMMIT o ROLLBACK nel suo corpo; una funzione gira sempre dentro la transazione di chi la chiama.

Cosa significa $$ in una funzione PostgreSQL?

$$ è il dollar quoting: segna l'inizio e la fine di una stringa letterale, così il corpo della funzione può contenere apici singoli senza farne l'escape. Puoi anche aggiungere un tag, come in $body$ ... $body$.

Come vedo il codice di una funzione PostgreSQL?

In psql, \df elenca le funzioni e \sf name stampa la definizione di una funzione. In SQL, SELECT pg_get_functiondef('name'::regproc); restituisce l'istruzione CREATE OR REPLACE FUNCTION completa.

Una funzione PostgreSQL può restituire più righe?

Sì. Dichiarala RETURNS TABLE (col type, ...) o RETURNS SETOF type e chiamala nella clausola FROM come una tabella: SELECT * FROM f(1);.

Illustrazione dei linguaggi di programmazione di Coddy

Impara a programmare con Coddy

Inizia