Scrittura di funzioni PL/pgSQL

Introduzione

PostgreSQL è molto più di un semplice motore RDBMS. È una piattaforma di sviluppo. Fornisce un linguaggio di programmazione molto potente e flessibile chiamato PL/pgSQL. Usando questo linguaggio puoi scrivere le tue funzioni definite dall’utente per ottenere livelli di astrazione e calcoli procedurali che sarebbero difficili da ottenere con il semplice SQL (e a volte impossibili da ottenere senza passare attraverso l’applicazione).

Scrivere funzioni

Nello schema public, fai clic destro sul nodo Funzioni e clicca su Crea Funzione. Si aprirà una scheda interna Query SQL, già contenente un modello SQL per aiutarti a creare la tua prima funzione PL/pgSQL.

Puoi consultare la documentazione di PostgreSQL su come scrivere funzioni definite dall’utente. Basta fare clic destro sul nodo Funzioni e cliccare su Doc: Functions — si aprirà la pagina di documentazione PostgreSQL pertinente nel tuo browser web (il browser predefinito del sistema nell’app desktop, oppure una nuova scheda quando esegui OmniDB in modalità browser tramite Distribuzione di omnidb-server).

Per ora, sostituiamo interamente questo modello SQL con il codice sorgente seguente:

CREATE OR REPLACE FUNCTION public.fnc_count_vowels (p_input text)
RETURNS integer LANGUAGE plpgsql AS
$function$
DECLARE
  str text;
  ret integer;
  i   integer;
  len integer;
BEGIN
  str := upper(p_input);
  ret := 0;
  i := 1;
  len := length(p_input);
  WHILE i <= len LOOP
    IF substr(str, i, 1) in ('A', 'E', 'I', 'O', 'U') THEN
      ret := ret + 1;
    END IF;
    i := i + 1;
  END LOOP;
  RETURN ret;
END;
$function$

Questo creerà una funzione chiamata fnc_count_vowels all’interno dello schema public. Questa funzione accetta un argomento di tipo testo chiamato p_input e conta quante vocali sono presenti in questa stringa. Poi restituisce questo conteggio.

Per creare la funzione, esegui il comando nella scheda interna Query SQL. Se l’operazione ha successo, la funzione apparirà sotto il nodo dell’albero Funzioni (puoi aggiornarlo facendo clic destro e poi cliccando su Aggiorna). Espandendo anche il nodo della funzione, puoi vedere il suo tipo di ritorno e il suo argomento.

Ora eseguiamo questa nuova funzione per la prima volta. Apri una semplice scheda interna Query SQL ed esegui la seguente query SQL:

SELECT public.fnc_count_vowels('The quick brown fox jumps over the lazy dog.')

Nota come la query restituisca un singolo valore, contenente il numero di vocali nel testo.

Facendo clic destro sul nodo della funzione, puoi vedere che sono disponibili azioni per modificarla, selezionarla ed eliminarla. Come probabilmente avrai intuito, ciascuna azione aprirà schede interne Query SQL con comodi modelli SQL già pronti.