Abbiamo gia’ visto come installare e gestire PostgreSQL e abbiamo accennato al potente linguaggio di programmazione PL/pgSQL.

Andiamo a studiare questo linguaggio di programmazione attraverso qualche semplice esempio.

Prima di iniziare dobbiamo connetterci al database ed abilitare il linguaggio plpgsql.

# psql -U alorenzi -t template1
template1=> create database test;
template1=> \c test
test=> CREATE LANGUAGE 'plpgsql';

Fatto questo passo (una sola volta) possiamo iniziare a scrivere il nostro primo

Hello world

Iniziamo con il piu’ classico dei programmi, l’hello world, una funzione che stampa a video un messaggio, “Hello world”.

Nella shell di PostgreSQL scriviamo (o copia-incolliamo) la funzione:

CREATE FUNCTION hello_world() RETURNS void AS $$
BEGIN
    RAISE NOTICE 'Hello world';  -- Prints  
    RETURN;
END;
$$ LANGUAGE plpgsql;

Per richiamare la funzione utilizziamo “SELECT“, postgres ci rispondera’ con un messaggio di notifica.

SELECT hello_world();
-- NOTICE:  Hello world

Ora pero’ questo script non ci serve piu’ DROPpiamolo.

DROP FUNCTION hello_world() ;
-- DROP FUNCTION

Calcoliamo l’IVA

Questa funzione accetta come parametro un numero con virgola (real) e ritorna il valore con l’aggiunta dell’iva al 22%

CREATE FUNCTION iva(imponibile REAL) RETURNS REAL AS $$
BEGIN
    RETURN imponibile * 1.22;
END;
$$ LANGUAGE plpgsql;
-- CREATE FUNCTION
SELECT iva(1022.30);
-- 1247.21

Possiamo modificare lo script per accettare anche quale aliquota applicare.

CREATE OR REPLACE FUNCTION iva(imponibile REAL, aliquota INT ) RETURNS REAL AS $$
BEGIN
    RETURN imponibile * (100+aliquota)/100;
END;
$$ LANGUAGE plpgsql;
-- CREATE FUNCTION
SELECT iva(1022.30, 22);
-- 1247.21
SELECT iva(1022.30, 10);
-- 1124.53
SELECT iva(1022.30, 4);
-- 1063.19

Lavoriamo con le tabelle

Usato come l’abbiamo usato fino ad ora non serve a molto, questi calcoli potremmo farli direttamente da PHP o Python.

Ma se volessimo ottenere il totale di una fattura con piu’ righe in cui ogni riga ci sono imponibile e iva da un linguaggio di programmazione esterno dovremmo fare una query, ricevere TUTTE le righe della fattura e fare il calcolo per ogniuna.

Sarebbe molto piu’ semplice da python fare una singola query del tipo:

SELECT totale_fattura(1);

Per farlo possiamo usare PL/pgSQL.

Prima di tutto creiamo una tabella con alcuni dati.

CREATE OR REPLACE TABLE righe_fatture (
  id INTEGER PRIMARY KEY,
  id_fattura INTEGER,
  descrizione VARCHAR(100),
  imponibile REAL,
  aliquota INTEGER
);
 
INSERT INTO righe_fatture VALUES
(1, 1, 'aaa', 1000.00, 22),
(2, 1, 'bbb', 30.00, 4),
(3, 1, 'ccc', 9.99, 22),
(4, 2, 'ddd', 13.00, 22),
(5, 2, 'eee', 100.00, 22),
(6, 3, 'fff', 28.00, 10),
(7, 3, 'ggg', 72.00, 04),
(8, 3, 'hhh', 12.00, 10),
(9, 3, 'iii', 99.00, 22);

Ora creiamo la funzione totale_fattura che accetti in ingresso l’id della fattura e ritorni il totale.

CREATE OR REPLACE FUNCTION totale_fattura(id_fattura INT ) RETURNS REAL AS $$
DECLARE
  totale REAL;
  subtotale REAL;
BEGIN
  totale := 0;
  FOR subtotale IN SELECT iva(imponibile, aliquota) FROM righe_fatture WHERE righe_fatture.id_fattura = id_fattura LOOP
    totale := totale + subtotale;
  END LOOP;
  RETURN totale;
END;
$$ LANGUAGE plpgsql;
-- CREATE FUNCTION
SELECT totale_fattura(1); 
-- 1263.39

Non solo select

Ci siamo accorti che facciamo spesso uso della funzione totale_fattura e, per ottimizzare, vorremmo avere un campo “totale” gia’ calcolato.

Procediamo quindi ad aggiungere la colonna nella tabella, poi con un singolo upate calcoliamo tutti i totali.

ALTER TABLE righe_fatture ADD totale REAL;
UPDATE righe_fatture SET totale = iva(imponibile, aliquota);

Trigger

Essendo un valore calcolato un programma potrebbe aggiungere un valore non consistente.

Ad esempio la query qui di seguito non darebbe errori:

INSERT INTO righe_fatture 
(id, id_fattura, descrizione, imponibile, aliquota, totale)
VALUES
(10, 1, 'aaa', 1.00, 22, 122.00);

Potremmo richiamare una funzione che calcola il totale della riga della fattura ogni volta che facciamo un inserimento o una modifica alla tabella.

CREATE FUNCTION add_total() RETURNS trigger AS $add_total$
BEGIN
  NEW.totale := iva(NEW.imponibile, NEW.aliquota);
  RETURN NEW;
END;
$add_total$ LANGUAGE plpgsql;

CREATE TRIGGER add_total BEFORE INSERT OR UPDATE ON righe_fatture
    FOR EACH ROW EXECUTE PROCEDURE add_total();

Ora procediamo all’inserimento di una riga senza specificare il totale.:

INSERT INTO righe_fatture 
(id, id_fattura, descrizione, imponibile, aliquota)
VALUES
(10, 1, 'aaa', 1000.00, 22);

Possiamo verificare che il campo totale e’ stato aggiornato correttamente

SELECT * FROM righe_fatture WHERE id = 10;
 10 |          1 | aaa         |       1000 |       22 |   1220

Documentazione

Potrete trovare approfondimenti sul sito ufficiale di PostgreSQL.

Voi usate gia’ PL/pgSQL? Che cosa ne pensate?
Fatecelo sapere con un messaggio qui sotto! 🙂

Info about author

Alessandro Lorenzi

RHCE Consultant at Extraordy