Creare e usare una funzione in Database di Azure per PostgreSQL

  • 5 minuti

Si è già appreso che PostgreSQL supporta linguaggi diversi. Le funzioni possono essere suddivise in quattro tipi diversi:

  • Funzioni scritte in SQL.
  • Funzioni del linguaggio procedurale, scritte in un linguaggio procedurale supportato, ad esempio PL.pgSQL.
  • Funzioni interne.
  • Funzioni del linguaggio C.

Inoltre, lo scopo della funzione può anche essere categorizzato come volatile, non modificabile o stabile.

Una funzione volatile (predefinita) può modificare il database e potrebbe non restituire necessariamente lo stesso risultato con gli stessi parametri di input ogni volta. Pertanto, ogni volta che questa funzione viene chiamata, deve essere rivalutata.

Una funzione stabile non può modificare il database e restituisce lo stesso risultato se sono stati passati gli stessi argomenti ed eseguiti all'interno della stessa istruzione. Se questa funzione viene chiamata più volte, Query Optimizer può usare i risultati dell'ultima chiamata.

Una funzione non modificabile non può modificare il database e restituisce gli stessi risultati se sono stati passati gli stessi argomenti, indipendentemente dalla query che lo chiama.

La volatilità di una funzione fa una grande differenza per l'efficienza con cui query optimizer lo gestisce.

Creare una funzione

Una funzione restituisce un singolo valore e può essere usata all'interno di un'istruzione SELECT.

La sintassi per la creazione di una funzione è:

CREATE [OR REPLACE] FUNCTION
myfunction ([inputparam] type {default})
RETURNS returntype AS
$$
SQL body
$$
LANGUAGE 'language_name';
CREATE FUNCTION

Come per le stored procedure, il simbolo $$ viene usato per iniziare e terminare la stringa.

Le funzioni accettano i parametri seguenti:

  • name : facoltativamente includere il nome dello schema.
  • argmode: modalità dell'argomento. Può essere IN, OUT, INOUT o VARIADIC. Il valore predefinito è IN. VARDIAC è un numero non definito di argomenti di input dello stesso tipo e viene seguito da argomenti OUT. Gli argomenti OUT e INOUT non possono essere usati insieme alle notazioni RETURNS TABLE.
  • argname: nome dell'argomento.
  • argtype: tipo di dati dell'argomento. Può essere di tipo base, composito o di dominio oppure fare riferimento a un tipo di colonna di tabella. Il tipo di colonna viene scritto come table_name.column_name%TYPE. Questo tipo di dati consente di rendere una funzione indipendente dalla modifica della definizione di tabella.
  • t_expr : valore predefinito (dello stesso tipo) se il parametro non è specificato. Solo i parametri IN e INOUT hanno un valore predefinito. Anche i parametri di input che seguono un parametro con un valore predefinito devono avere valori predefiniti.
  • tipo ret: tipo di dati restituito, che può essere un tipo di base, composito o di dominio oppure fare riferimento a un tipo di colonna di tabella. Se la funzione non restituisce un valore, specificare il tipo restituito come void. Quando sono presenti parametri OUT o INOUT, è possibile omettere la clausola RETURNS. Se presente, deve accettare il tipo di risultato implicito nei parametri di output: RECORD se sono presenti più parametri di output o lo stesso tipo del singolo parametro di output. Il modificatore SETOF indica che la funzione restituisce un set di elementi, anziché un singolo elemento. Il tipo di una colonna viene fatto riferimento scrivendo table_name.
  • column_name : nome di una colonna di output nella sintassi RETURNS TABLE. Questo parametro dichiara un parametro OUT denominato, ad eccezione del fatto che RETURNS TABLE implica anche RETURNS SETOF.
  • column_type: tipo di dati di una colonna di output nella sintassi RETURNS TABLE.
  • lang_name : lingua usata per scrivere la routine. Il valore predefinito è sql se viene specificato sql_body. Può essere sql, c, interno o il nome di un linguaggio procedurale definito dall'utente, ad esempio plpgsql.

Usare le parole chiave IMMUTABLE, STABLE o VOLATILE come hint all'ottimizzatore di query sulla funzione. VOLATILE è l'impostazione predefinita.

Chiamare una funzione

Una funzione può essere usata in una query passando a essa tutti i parametri pertinenti. Per esempio:

SELECT myfunction(3), CatID, CatName
    FROM myCats

Funzioni predefinite

PostgreSQL include molte funzioni predefinite che è possibile usare nelle query. Questi argomenti riguardano l'esecuzione di confronti, l'aggregazione di dati, le funzioni matematiche e così via. Per un elenco completo delle funzioni PostgreSQL, vedere la documentazione online.

Un esempio di funzione stringa predefinita è substring.

substring (*string* text [ FROM *start* integer ] [ FOR *count* integer ] ) → text

Questa funzione accetta tre parametri di input:

  • String (tipo Text)
  • FROM start (tipo Integer)
  • FOR count (tipo Integer)

Substring restituisce parte del testo di input, che inizia dal carattere start e termina dopo i caratteri count. Per esempio:

substring('Thomas' from 2 for 3) → hom
substring('Thomas' from 3) → omas
substring('Thomas' for 2) → Th

Questa funzione è la stessa di substr:

substr ( *string* text, *start* integer [, *count* integer ] ) → text
substr('alphabet', 3) → phabet
substr('alphabet', 3, 2) → ph

Nota

Se si ha familiarità con le funzioni, si noterà che la prima versione usa parole chiave anziché virgole per separare gli argomenti. PostgreSQL offre entrambe le versioni di queste funzioni.