PL/pgSQL

Joan Puigcerver Ibáñez

j.puigcerveribanez@edu.gva.es

Llicència: CC BY-NC-SA 4.0

(Reconeixement - NoComercial - CompartirIgual) 🅭

Introducció¶

PL/pgSQL (Procedural Language/PostgreSQL) és el llenguatge procedural natiu de PostgreSQL. Permet combinar sentències SQL amb les estructures pròpies de la programació, com les variables, els condicionals o els bucles.

Gràcies a això, es poden implementar lògica de negoci, validacions, processos repetitius i gestió d'errors directament dins de la base de dades, sense dependre d'una aplicació externa.

SQL treballa amb dades; PL/pgSQL treballa amb processos.

SQL i PL/pgSQL¶

SQL és un llenguatge declaratiu: cada sentència indica quin resultat es vol, però no com obtindre'l. PL/pgSQL, en canvi, és procedural: permet encadenar sentències, prendre decisions i repetir accions. La taula següent resumeix les diferències principals:

SQL	PL/pgSQL
Declaratiu	Procedural
Orientat a dades	Orientat a processos
Sense control de flux	`IF`, bucles, excepcions
Sense gestió d'errors	Gestió d'excepcions
Sentències independents	Blocs de codi

Estructura d'un bloc PL/pgSQL¶

El codi PL/pgSQL s'organitza en blocs. En crear una funció o un procediment, cal indicar el llenguatge explícitament amb LANGUAGE plpgsql, i el codi del bloc s'escriu entre delimitadors, habitualment $$.

Cada bloc té fins a quatre seccions:

DECLARE (opcional): declaració de variables.
BEGIN (obligatòria): codi executable.
EXCEPTION (opcional): gestió d'errors.
END; (obligatòria): final del bloc.

Un bloc anònim, és a dir, sense nom, s'executa directament amb DO:

DO $$
BEGIN
    RAISE NOTICE 'Hola PL/pgSQL';
END;
$$;

Variables i tipus de dades¶

Les variables es declaren en la secció DECLARE, amb un nom i un tipus de dades. Els tipus més habituals són NUMERIC, INTEGER, VARCHAR, DATE i BOOLEAN:

DO $$
DECLARE
    v_name   VARCHAR(50);
    v_salary NUMERIC;
BEGIN
END;
$$;

Tipus ancorats a la base de dades¶

Si una variable ha de guardar el valor d'una columna, és millor no escriure'n el tipus a mà. En lloc d'això, es pot ancorar el tipus a la base de dades, de manera que, si la columna canvia, la variable s'adapta automàticament:

%TYPE: pren el tipus d'una columna.
%ROWTYPE: pren l'estructura d'una fila completa d'una taula.

v_salary employees.salary%TYPE;

Ús de SQL dins de PL/pgSQL (`SELECT INTO`)¶

Per guardar el resultat d'una consulta en variables, s'utilitza la clàusula INTO:

SELECT salary
INTO v_salary
FROM employees
WHERE employee_id = 100;

Preparació de l'esquema dels exemples

Els exemples d'aquests apunts utilitzen l'esquema següent:

ud4/ScriptDBEmployeesPostgreSQL.sql

--------------------------------------------------
-- LIMPIEZA PREVIA (por si existen)
--------------------------------------------------

DROP TABLE IF EXISTS employees CASCADE;
DROP TABLE IF EXISTS positions CASCADE;
DROP TABLE IF EXISTS work_centers CASCADE;

--------------------------------------------------
-- TABLA: WORK_CENTERS
--------------------------------------------------

CREATE TABLE work_centers (
    center_id      INTEGER PRIMARY KEY,
    center_name    VARCHAR(100) NOT NULL,
    city           VARCHAR(50),
    phone          VARCHAR(20)
);

--------------------------------------------------
-- TABLA: POSITIONS
--------------------------------------------------

CREATE TABLE positions (
    position_id    INTEGER PRIMARY KEY,
    position_name  VARCHAR(50) NOT NULL,
    base_salary    NUMERIC(8,2)
);

--------------------------------------------------
-- TABLA: EMPLOYEES
--------------------------------------------------

CREATE TABLE employees (
    employee_id    INTEGER PRIMARY KEY,
    first_name     VARCHAR(50),
    last_name      VARCHAR(50),
    hire_date      DATE,
    salary         NUMERIC(8,2),
    center_id      INTEGER REFERENCES work_centers(center_id),
    position_id    INTEGER REFERENCES positions(position_id),
    email          VARCHAR(50)
);

--------------------------------------------------
-- DATOS DE EJEMPLO: WORK_CENTERS
--------------------------------------------------

INSERT INTO work_centers VALUES (1, 'Centro Madrid Norte', 'Madrid', '910000001');
INSERT INTO work_centers VALUES (2, 'Centro Barcelona Este', 'Barcelona', '930000002');
INSERT INTO work_centers VALUES (3, 'Centro Sevilla Sur', 'Sevilla', '950000003');

--------------------------------------------------
-- DATOS DE EJEMPLO: POSITIONS
--------------------------------------------------

INSERT INTO positions VALUES (1, 'Desarrollador Junior', 18000);
INSERT INTO positions VALUES (2, 'Desarrollador Senior', 32000);
INSERT INTO positions VALUES (3, 'Administrador de Sistemas', 30000);
INSERT INTO positions VALUES (4, 'Jefe de Proyecto', 38000);

--------------------------------------------------
-- DATOS DE EJEMPLO: EMPLOYEES
--------------------------------------------------

INSERT INTO employees VALUES
(100, 'Juan', 'Pérez', DATE '2020-02-15', 18500, 1, 1, 'juan@work.es');

INSERT INTO employees VALUES
(101, 'Ana', 'García', DATE '2019-06-10', 33000, 1, 2, 'ana@work.es');

INSERT INTO employees VALUES
(102, 'Luis', 'Martín', DATE '2021-01-20', 30000, 2, 3, 'luis@work.es');

INSERT INTO employees VALUES
(103, 'María', 'López', DATE '2018-09-05', 39000, 2, 4, 'maria@work.es');

INSERT INTO employees VALUES
(104, 'Carlos', 'Sánchez', DATE '2022-03-12', 18000, 3, 1, 'carlos@work.es');

INSERT INTO employees VALUES
(105, 'Elena', 'Ruiz', DATE '2020-11-30', 32000, 3, 2, 'elena@work.es');

COMMIT;

SELECT INTO no llança cap error si troba 0 files o més d'una.

Per exigir exactament una fila i llançar un error en cas contrari, cal afegir la clàusula STRICT:

SELECT salary
INTO STRICT v_salary
FROM employees
WHERE employee_id = 100;

Sense STRICT: si no hi ha cap fila, la variable queda a NULL; si n'hi ha diverses, s'assigna la primera.
Amb STRICT: es llancen les excepcions NO_DATA_FOUND i TOO_MANY_ROWS, respectivament.

Gestió d'excepcions¶

Les excepcions permeten controlar els errors que es produeixen en temps d'execució. Quan es produeix un error dins del bloc, l'execució salta a la secció EXCEPTION, on es pot tractar segons el tipus d'error:

EXCEPTION
    WHEN NO_DATA_FOUND THEN
        RAISE NOTICE 'No s''han trobat dades';

Les excepcions més habituals són:

NO_DATA_FOUND: la consulta no ha retornat cap fila (amb STRICT).
TOO_MANY_ROWS: la consulta ha retornat més d'una fila (amb STRICT).
DIVISION_BY_ZERO: s'ha intentat dividir entre zero.
OTHERS: qualsevol altre error.

Documentació oficial: Errors and Messages – PostgreSQL

Exercici: SELECT INTO STRICT amb excepcions

Obtín el salari de l'empleat amb identificador 100 de la taula employees i tracta els casos en què no existeix o n'hi ha més d'un.

Solució

DO $$
DECLARE
    v_salari NUMERIC;
BEGIN
    SELECT salary
    INTO STRICT v_salari
    FROM employees
    WHERE employee_id = 100;

    RAISE NOTICE 'Salari de l''empleat: %', v_salari;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        RAISE NOTICE 'Empleat no trobat';
    WHEN TOO_MANY_ROWS THEN
        RAISE NOTICE 'Hi ha més d''un empleat amb eixe ID';
END;
$$;

Estructures de control¶

Les estructures de control permeten decidir quines sentències s'executen i quantes vegades.

Condicionals (`IF`)¶

La sentència IF executa un bloc de codi o un altre segons es complisca una condició:

IF condicio THEN
   ...
ELSIF altra_condicio THEN
   ...
ELSE
   ...
END IF;

Bucles¶

Els bucles repeteixen un bloc de codi. El bucle FOR és el més utilitzat, ja que recorre un rang de valors o el resultat d'una consulta sense haver de controlar cap comptador:

FOR i IN 1..10 LOOP
    ...
END LOOP;

El bucle WHILE, en canvi, es repeteix mentre es complisca una condició:

WHILE condicio LOOP
    ...
END LOOP;

Exercici: SELECT INTO, IF/ELSE i bucle

Crea un bloc que:

Obtinga el salari mitjà de la taula employees.
Recórrega tots els empleats.
Per a cada empleat, mostre el nom i el salari, i indique si està «Per damunt de la mitjana» o «Per davall de la mitjana».

Solució

DO $$
DECLARE
    v_avg_salary NUMERIC;
BEGIN
    SELECT AVG(salary) INTO v_avg_salary FROM employees;

    FOR r IN (SELECT first_name, salary FROM employees) LOOP
        IF r.salary >= v_avg_salary THEN
            RAISE NOTICE '% - % (Per damunt de la mitjana)', r.first_name, r.salary;
        ELSE
            RAISE NOTICE '% - % (Per davall de la mitjana)', r.first_name, r.salary;
        END IF;
    END LOOP;
END;
$$;

Funcions i procediments¶

Els blocs anònims s'executen una vegada i desapareixen. Per reutilitzar el codi, cal guardar-lo en la base de dades com a funció o com a procediment. PostgreSQL distingeix entre tots dos (els procediments existeixen des de la versió 11).

Funcions (`CREATE FUNCTION`)¶

Una funció és un bloc amb nom que sempre retorna un valor, indicat amb RETURNS. Com que retorna un valor, es pot utilitzar dins de sentències SQL. En canvi, no pot executar COMMIT ni ROLLBACK.

CREATE OR REPLACE FUNCTION augment_salari(p_sal NUMERIC)
RETURNS NUMERIC AS $$
BEGIN
    RETURN p_sal * 1.10;
END;
$$ LANGUAGE plpgsql;

Una vegada creada, la funció s'utilitza com qualsevol funció de SQL:

SELECT augment_salari(2000);

SELECT
    first_name || ' ' || last_name AS empleat,
    salary AS salari_actual,
    augment_salari(salary) AS salari_amb_increment
FROM employees;

Procediments (`CREATE PROCEDURE`)¶

Un procediment és un bloc amb nom que no retorna cap valor, encara que pot tindre paràmetres OUT o INOUT. S'invoca amb CALL, mai des d'una consulta SQL. A diferència de les funcions, pot executar COMMIT i ROLLBACK, sempre que s'invoque com a sentència de nivell superior (no dins d'un altre bloc transaccional).

CREATE OR REPLACE PROCEDURE saludar(p_nom VARCHAR) AS $$
BEGIN
    RAISE NOTICE 'Hola %', p_nom;
END;
$$ LANGUAGE plpgsql;

CALL saludar('Juan');

Els paràmetres poden ser de tres tipus, segons la direcció en què passen les dades:

IN: només d'entrada (valor per defecte).
OUT: només d'eixida.
INOUT: d'entrada i d'eixida.

CREATE OR REPLACE PROCEDURE salari_mig_departament(
    p_dept_id  IN NUMERIC,
    p_sal_mig  OUT NUMERIC
) AS $$
BEGIN
    SELECT AVG(salary) INTO p_sal_mig
    FROM employees
    WHERE center_id = p_dept_id;
END;
$$ LANGUAGE plpgsql;

CALL salari_mig_departament(1, NULL);

Cursors en PL/pgSQL¶

Un cursor permet recórrer, fila a fila, el resultat d'una consulta que retorna múltiples files.

Cursor implícit (`FOR` sobre una consulta)¶

És la forma més senzilla d'iterar el resultat d'una consulta: PostgreSQL obri, recorre i tanca el cursor automàticament.

DO $$
DECLARE
    r RECORD;
BEGIN
    FOR r IN (SELECT first_name, salary FROM employees) LOOP
        RAISE NOTICE '% - %', r.first_name, r.salary;
    END LOOP;
END;
$$;

Cursor explícit (`CURSOR`)¶

Un cursor explícit es declara amb CURSOR i es gestiona manualment amb OPEN, FETCH i CLOSE. Dona més control que el cursor implícit, per exemple, per aturar el recorregut a mitjan camí. La variable especial FOUND indica si l'última FETCH ha obtingut una fila:

DO $$
DECLARE
    c_emps CURSOR FOR
        SELECT first_name, salary FROM employees;
    r_emp RECORD;
BEGIN
    OPEN c_emps;
    LOOP
        FETCH c_emps INTO r_emp;
        EXIT WHEN NOT FOUND;
        RAISE NOTICE '% - %', r_emp.first_name, r_emp.salary;
    END LOOP;
    CLOSE c_emps;
END;
$$;

Exercici: Cursor amb condició i càlcul

Crea un bloc amb un cursor explícit que:

Recórrega tots els empleats.
Calcule un bonus del 10 % per als empleats amb un salari inferior a 30000.
Mostre el nom, el salari i el bonus de cada empleat.

Solució

DO $$
DECLARE
    c_emps CURSOR FOR
        SELECT first_name, salary FROM employees;
    r_emp   RECORD;
    v_bonus NUMERIC;
BEGIN
    OPEN c_emps;
    LOOP
        FETCH c_emps INTO r_emp;
        EXIT WHEN NOT FOUND;

        IF r_emp.salary < 30000 THEN
            v_bonus := r_emp.salary * 0.10;
        ELSE
            v_bonus := 0;
        END IF;

        RAISE NOTICE '% - % - Bonus: %', r_emp.first_name, r_emp.salary, v_bonus;
    END LOOP;
    CLOSE c_emps;
END;
$$;

Bones pràctiques en PL/pgSQL¶

Un codi PL/pgSQL fàcil de mantindre segueix unes quantes pautes:

Noms descriptius: les variables, els paràmetres i els procediments han de tindre noms clars.
Documentació: el codi ha d'estar comentat, especialment la lògica menys evident.
Gestió d'excepcions: els errors previsibles s'han de tractar explícitament.
Lògica mesurada: no s'ha d'abusar de la lògica en la base de dades (vegeu l'exercici final).
Tipus ancorats: %TYPE i %ROWTYPE eviten errors quan canvia l'estructura de les taules.
SELECT INTO STRICT: s'ha d'utilitzar quan cal garantir que la consulta retorna exactament una fila.

Ús pràctic en el dia a dia¶

PL/pgSQL també és útil per a tasques d'administració, com finalitzar les sessions que causen problemes.

Finalitzar una sessió¶

Una sessió es finalitza amb la funció pg_terminate_backend, indicant el PID del procés de la connexió. El procediment següent l'encapsula i en gestiona els errors:

CREATE OR REPLACE PROCEDURE kill_session(p_pid INTEGER) AS $$
BEGIN
    PERFORM pg_terminate_backend(p_pid);
    RAISE NOTICE 'Sessió % finalitzada', p_pid;
EXCEPTION
    WHEN OTHERS THEN
        RAISE NOTICE 'Error: %', SQLERRM;
END;
$$ LANGUAGE plpgsql;

PERFORM s'utilitza per cridar una funció sense recollir el seu valor de retorn.

Cas pràctic: identificar i finalitzar sessions bloquejants¶

Quan una sessió manté un bloqueig durant massa temps, les altres sessions que necessiten les mateixes dades es queden esperant. Per resoldre-ho, primer cal identificar quina sessió bloqueja i, després, finalitzar-la:

-- 1. Consultar les sessions bloquejades i qui les bloqueja
SELECT blocked.pid       AS bloquejada_pid,
       blocked.usename   AS usuari,
       blocking.pid      AS bloquejant_pid,
       blocking.usename  AS usuari_bloquejant
FROM pg_locks blocked_locks
JOIN pg_stat_activity blocked  ON blocked.pid = blocked_locks.pid
JOIN pg_locks blocking_locks   ON blocking_locks.locktype = blocked_locks.locktype
                               AND blocking_locks.pid != blocked_locks.pid
                               AND blocking_locks.granted
JOIN pg_stat_activity blocking ON blocking.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted;

-- Suposem que la sessió bloquejant té el pid 456

-- 2. Invocar el procediment per finalitzar-la
CALL kill_session(456);

Exercici: Reflexió final

Per què no és recomanable posar tota la lògica de negoci en la base de dades?

Solució

Dificulta el manteniment, redueix la flexibilitat i acobla massa l'aplicació a la base de dades.

📌 Aquest document pot quedar desactualitzat després d’imprimir-lo. Pots consultar la versió més recent a la pàgina web.

🌿 Abans d’imprimir aquest document, considera si és realment necessari. Redueix el consum de paper i ajuda a protegir el nostre entorn.

PL/pgSQL

Introducció¶

SQL i PL/pgSQL¶

Estructura d'un bloc PL/pgSQL¶

Variables i tipus de dades¶

Tipus ancorats a la base de dades¶

Ús de SQL dins de PL/pgSQL (SELECT INTO)¶

Gestió d'excepcions¶

Estructures de control¶

Condicionals (IF)¶

Bucles¶

Funcions i procediments¶

Funcions (CREATE FUNCTION)¶

Procediments (CREATE PROCEDURE)¶

Cursors en PL/pgSQL¶

Cursor implícit (FOR sobre una consulta)¶

Cursor explícit (CURSOR)¶

Bones pràctiques en PL/pgSQL¶

Ús pràctic en el dia a dia¶

Finalitzar una sessió¶

Cas pràctic: identificar i finalitzar sessions bloquejants¶

Ús de SQL dins de PL/pgSQL (`SELECT INTO`)¶

Condicionals (`IF`)¶

Funcions (`CREATE FUNCTION`)¶

Procediments (`CREATE PROCEDURE`)¶

Cursor implícit (`FOR` sobre una consulta)¶

Cursor explícit (`CURSOR`)¶