El Projecte | Sobre nosaltres | Contribuir | Donacions | Llicència

HOME

Tot el que has escrit fins ara és una consulta: s'envia, s'executa i s'oblida. Aquesta lliçó va del contrari — codi amb nom que viu dins de la base de dades i que s'invoca com si fos part del llenguatge. Aquí acabarà vivint la confirmació de comanda que a 09-03 vas escriure a mà, amb el seu BEGIN, les seves quatre sentències i el seu COMMIT, i que fins ara depenia que qui l'executés no es deixés cap pas.

Veuràs la diferència real entre una funció i un procediment a PostgreSQL 11+, el just de PL/pgSQL per escriure alguna cosa útil sense convertir això en un curs de programació, i tres exemples de BotigaVerda en ordre de dificultat que acaben en sp_confirmar_comanda. I, sobretot, la discussió honesta: què hi guanya un equip posant lògica a la base de dades, què hi perd, i on és la frontera. Perquè aquesta és l'eina del mòdul que més fàcil és fer servir malament.

Contingut

Funció enfront de procediment
CREATE FUNCTION amb LANGUAGE sql
PL/pgSQL en una taula de referència
Paràmetres, tipus de retorn, sobrecàrrega i esborrat
fn_total_comanda: una funció escalar
fn_vendes_per_categoria: una funció que retorna taula
sp_confirmar_comanda: el procediment de 09-03
Volatilitat i SECURITY DEFINER
La discussió honesta: què posar-hi a dins i què no
Errors habituals i consells
Exercicis
Conclusió

Funció enfront de procediment

Fins a PostgreSQL 10 només existien funcions, i es feien servir per a tot. Des de la 11 hi ha també procediments, i la confusió entre tots dos és constant. La taula que la resol:

	Funció (`CREATE FUNCTION`)	Procediment (`CREATE PROCEDURE`)
S'invoca amb	`SELECT fn(...)` o dins d'una consulta	`CALL sp(...)`, com a sentència solta
Retorna	Sempre alguna cosa: escalar, fila, taula o `void`	Res (o `INOUT`)
Es pot fer servir en un `SELECT`?	Sí: és una expressió més	No
Controla transaccions?	No. Corre dins de la de qui la crida	Sí: pot fer `COMMIT` i `ROLLBACK`
Volatilitat declarable	Sí (`IMMUTABLE`/`STABLE`/`VOLATILE`)	No
Per a què serveix	Calcular un valor o retornar un conjunt	Executar un procés: passos, lots, manteniment

La línia divisòria és el control de transaccions. Una funció s'executa dins de la transacció de qui la crida; si aquella transacció es desfà, tot el que va fer la funció es desfà amb ella. Un procediment cridat amb CALL fora d'una transacció explícita pot confirmar pel seu compte, cosa que permet escriure un procés per lots que confirma cada mil files sense mantenir una transacció gegant oberta (09-01).

La regla pràctica: si retorna una dada, funció; si fa una feina, procediment.

`CREATE FUNCTION` amb `LANGUAGE sql`

La forma més senzilla no necessita cap llenguatge procedimental: és una consulta amb nom i paràmetres.

CREATE OR REPLACE FUNCTION fn_facturacio_pais(p_pais TEXT)
RETURNS NUMERIC LANGUAGE sql STABLE AS $$
    SELECT COALESCE(ROUND(SUM(lc.quantitat * lc.preu_unitari * (1 - lc.descompte)), 2), 0)
    FROM   linies_comanda AS lc
    JOIN   comandes       AS co ON co.id = lc.comanda_id
    JOIN   clients        AS c  ON c.id  = co.client_id
    WHERE  c.pais = p_pais;
$$;

SELECT fn_facturacio_pais('Espanya')  AS espanya,
       fn_facturacio_pais('Portugal') AS portugal,
       fn_facturacio_pais('França')   AS franca;

espanya	portugal	franca
433.70	156.48	137.77

433,70 + 156,48 + 137,77 = 727,95 €: les xifres per país que ja vas calcular a 07-04, ara encapsulades. Quatre detalls de sintaxi que es repetiran tota la lliçó:

$$ ... $$ delimita el cos. És el dollar quoting de 01-03: a dins hi pots escriure cometes simples sense escapar-les. Si el cos conté $$, fes servir una etiqueta: $fn$ ... $fn$.
Els paràmetres es nomenen amb prefix (p_pais) per convenció, perquè no xoquin amb noms de columna. Si un paràmetre es digués pais, PostgreSQL no sabria a què et refereixes al WHERE.
RETURNS NUMERIC declara el tipus. Amb LANGUAGE sql, el resultat és el de l'última sentència.
STABLE és la volatilitat (apartat 8).

(Totes les funcions i procediments d'aquesta lliçó són exemples: no formen part de l'esquema canònic de BotigaVerda de 01-06.)

PL/pgSQL en una taula de referència

LANGUAGE sql no sap decidir ni repetir. Per a això hi ha PL/pgSQL, el llenguatge procedimental que porta PostgreSQL de sèrie. Això és l'essencial —el just per escriure alguna cosa útil— i no cal res més per a aquesta lliçó:

Element	Sintaxi	Nota
Estructura	`DECLARE ... BEGIN ... EXCEPTION ... END;`	El bloc `DECLARE` és opcional; `BEGIN`/`END` no són els d'una transacció
Declarar	`v_total NUMERIC(10,2) := 0;`	Tipus explícit, o `%TYPE` / `%ROWTYPE` per copiar-lo d'una columna o taula
Assignar	`v_total := 12.50;`	Amb `:=`. I `SELECT ... INTO v_total` per assignar des d'una consulta
Condicional	`IF cond THEN ... ELSIF cond THEN ... ELSE ... END IF;`	Compte: és `ELSIF`, no `ELSEIF` ni `ELSE IF`
Bucle sobre consulta	`FOR r IN SELECT ... LOOP ... END LOOP;`	`r` es declara sola i és del tipus de la fila
Altres bucles	`LOOP ... EXIT WHEN cond; END LOOP;`, `WHILE`, `FOR i IN 1..10`
Retornar	`RETURN expr;` · `RETURN QUERY SELECT ...;` · `RETURN NEXT r;`	`RETURN QUERY` per a funcions de conjunt
Missatges	`RAISE NOTICE 'stock: %', v_stock;`	`%` és el marcador de substitució. Nivells: `DEBUG`, `LOG`, `NOTICE`, `WARNING`
Error	`RAISE EXCEPTION 'Sense stock de %', v_id USING ERRCODE = 'P0001';`	Avorta la transacció
Capturar	`EXCEPTION WHEN unique_violation THEN ... WHEN OTHERS THEN ...`
Files afectades	`GET DIAGNOSTICS v_n = ROW_COUNT;`	L'`UPDATE N` de 05-03, ara en una variable
Variables implícites	`FOUND` (l'última consulta ha trobat res?), `SQLERRM`, `SQLSTATE`

Dos avisos que estalvien hores. El primer: BEGIN i END a PL/pgSQL delimiten un bloc de codi, no una transacció; la paraula coincideix i confon tothom. El segon: un bloc amb EXCEPTION crea internament un SAVEPOINT (09-03) i té cost, així que no embolcallis amb EXCEPTION un bucle d'un milió d'iteracions si no cal.

Paràmetres, tipus de retorn, sobrecàrrega i esborrat

Aspecte	Formes
Modes de paràmetre	`IN` (per omissió), `OUT` (retorna per ell), `INOUT` (entra i surt), `VARIADIC` (nombre variable)
Valor per defecte	`p_any INTEGER DEFAULT 2025` — els que en tinguin han d'anar al final
Crida per nom	`fn_vendes_per_categoria(p_any => 2026)`, molt llegible amb diversos paràmetres
Retorn escalar	`RETURNS NUMERIC`, `RETURNS TEXT`, `RETURNS void`
Retorn de conjunt	`RETURNS SETOF productes` (files d'una taula existent) · `RETURNS TABLE(col tipus, ...)` (defineix les columnes allà mateix)
Sobrecàrrega	Diverses funcions amb el mateix nom i paràmetres diferents. Es resol per tipus
Esborrat	`DROP FUNCTION fn_total_comanda(INTEGER);` — cal donar-ne la signatura si està sobrecarregada

RETURNS TABLE(...) enfront de RETURNS SETOF record: la primera declara els noms i tipus de les columnes dins de la funció, i qui la crida escriu simplement SELECT * FROM fn(...). La segona obliga a descriure l'estructura a cada crida —SELECT * FROM fn(...) AS t(id INT, nom TEXT)—, cosa incòmoda i fràgil. Fes servir RETURNS TABLE tret que la forma del resultat depengui de debò dels arguments.

Sobre la sobrecàrrega, un advertiment: és còmoda però es torna traïdora amb els tipus. fn(1) i fn(1.0) es poden resoldre a funcions diferents, i DROP FUNCTION fn sense signatura dona function name "fn" is not unique. Prefereix noms diferents a sobrecarregar, tret que la sobrecàrrega sigui evident.

`fn_total_comanda`: una funció escalar

El primer exemple, i el que més es farà servir: el total d'una comanda, amb l'expressió canònica d'import.

CREATE OR REPLACE FUNCTION fn_total_comanda(p_comanda_id INTEGER)
RETURNS NUMERIC(10,2) LANGUAGE plpgsql STABLE AS $$
DECLARE
    v_total NUMERIC(10,2);
BEGIN
    SELECT COALESCE(ROUND(SUM(lc.quantitat * lc.preu_unitari * (1 - lc.descompte)), 2), 0)
    INTO   v_total
    FROM   linies_comanda AS lc
    WHERE  lc.comanda_id = p_comanda_id;

    IF v_total IS NULL THEN
        RAISE NOTICE 'La comanda % no té línies', p_comanda_id;
        RETURN 0;
    END IF;
    RETURN v_total;
END;
$$;

I aquí hi ha la seva gràcia: es fa servir com una columna més, dins de qualsevol consulta.

SELECT co.id, c.nom || ' ' || c.cognoms AS client, co.estat,
       fn_total_comanda(co.id) AS total_productes,
       co.despeses_enviament,
       fn_total_comanda(co.id) + co.despeses_enviament AS total_comanda
FROM   comandes AS co
JOIN   clients  AS c ON c.id = co.client_id
WHERE  co.id IN (1, 8, 12, 20)
ORDER  BY co.id;

id	client	estat	total_productes	despeses_enviament	total_comanda
1	Lucía Martínez Soler	lliurat	42.10	4.95	47.05
8	Sofia Moreira Costa	lliurat	64.88	9.90	74.78
12	Julien Moreau	lliurat	66.90	12.50	79.40
20	Camille Dubois	pendent	22.60	12.50	35.10

Els quatre totals coincideixen amb els de 07-04: la comanda 12 és el tiquet màxim (66,90 €) i la 20 el mínim (22,60 €).

I aquí hi ha el perill, dit aviat: SELECT id, fn_total_comanda(id) FROM comandes executa la funció una vegada per fila — 20 consultes independents contra linies_comanda. És exactament la subconsulta correlacionada de 07-02, ara amagada darrere d'un nom bonic. Per a un informe de 20 comandes tant és; per a un de dos milions, és la diferència entre 200 ms i mitja hora. Una funció escalar dins d'un SELECT massiu és un JOIN disfressat, i gairebé sempre és millor escriure el JOIN (o fer servir la vista de 10-01).

`fn_vendes_per_categoria`: una funció que retorna taula

Amb RETURNS TABLE, una funció es comporta com una taula parametritzada — una cosa a mig camí entre una vista i una consulta solta, i que les vistes no poden fer perquè una vista no accepta paràmetres.

CREATE OR REPLACE FUNCTION fn_vendes_per_categoria(p_any INTEGER DEFAULT 2025)
RETURNS TABLE (categoria_id INTEGER, categoria VARCHAR, unitats BIGINT, facturacio NUMERIC)
LANGUAGE plpgsql STABLE AS $$
BEGIN
    RETURN QUERY
    SELECT cat.id, cat.nom, SUM(lc.quantitat),
           ROUND(SUM(lc.quantitat * lc.preu_unitari * (1 - lc.descompte)), 2)
    FROM   linies_comanda AS lc
    JOIN   comandes   AS co  ON co.id  = lc.comanda_id
    JOIN   productes  AS p   ON p.id   = lc.producte_id
    JOIN   categories AS cat ON cat.id = p.categoria_id
    WHERE  EXTRACT(YEAR FROM co.data_comanda) = p_any
    GROUP  BY cat.id, cat.nom
    ORDER  BY 4 DESC;
END;
$$;

SELECT * FROM fn_vendes_per_categoria(2025);

categoria_id	categoria	unitats	facturacio
1	Alimentació	43	215.67
4	Begudes	22	146.55
2	Cosmètica natural	13	128.22
3	Llar sostenible	10	88.58
5	Higiene personal	7	24.50

Sumen 603,52 €, la facturació del 2025. I amb SELECT * FROM fn_vendes_per_categoria(2026) només en surten quatre categories —Begudes 48,73 €, Alimentació 40,60 €, Cosmètica natural 28,10 € i Higiene personal 7,00 €—, que sumen els 124,43 € del 2026: en els dos primers mesos de l'any no s'ha venut res de Llar sostenible.

Com qualsevol taula, es pot filtrar i unir: SELECT * FROM fn_vendes_per_categoria(2025) WHERE facturacio > 100; retorna tres files.

Quan funció i quan vista (10-01): si el resultat no depèn de cap paràmetre, vista. Si depèn d'un argument —l'any, el país, un rang de dates—, funció que retorna taula. I si el paràmetre només serveix per filtrar una columna que la vista ja exposa, vista i WHERE, que el planificador optimitza millor.

`sp_confirmar_comanda`: el procediment de 09-03

Aquest és el destí que 09-03 i 09-05 van anunciar. La confirmació d'una comanda són diverses operacions que han d'anar juntes: crear la capçalera, inserir les línies, descomptar l'estoc comprovant que no quedi negatiu i marcar la comanda com a pagada. Si alguna cosa falla, no ha de quedar res a mitges.

CREATE OR REPLACE PROCEDURE sp_confirmar_comanda(
    p_client_id       INTEGER,
    p_empleat_id      INTEGER,
    p_metode_pagament VARCHAR,
    p_enviament       NUMERIC,
    p_productes       INTEGER[],       -- ids de producte
    p_quantitats      INTEGER[],       -- quantitats, en el mateix ordre
    INOUT p_comanda_id INTEGER DEFAULT NULL   -- retorna l'id creat
)
LANGUAGE plpgsql
AS $$
DECLARE
    v_i INTEGER;  v_preu NUMERIC(10,2);  v_stock INTEGER;  v_nom TEXT;
BEGIN
    IF array_length(p_productes, 1) IS DISTINCT FROM array_length(p_quantitats, 1) THEN
        RAISE EXCEPTION 'Les llistes de productes i quantitats no tenen la mateixa mida';
    END IF;

    -- 1. Capçalera de la comanda
    INSERT INTO comandes (client_id, empleat_id, data_comanda, estat, metode_pagament, despeses_enviament)
    VALUES (p_client_id, p_empleat_id, CURRENT_DATE, 'pendent', p_metode_pagament, p_enviament)
    RETURNING id INTO p_comanda_id;

    -- 2. Una línia per producte, descomptant estoc
    FOR v_i IN 1 .. array_length(p_productes, 1) LOOP

        -- Bloquejo la fila del producte abans de decidir (patró de 09-05)
        SELECT p.preu, p.stock, p.nom INTO v_preu, v_stock, v_nom
        FROM   productes AS p
        WHERE  p.id = p_productes[v_i] AND p.actiu
        FOR UPDATE;

        IF NOT FOUND THEN
            RAISE EXCEPTION 'El producte % no existeix o està descatalogat', p_productes[v_i];
        END IF;

        IF v_stock < p_quantitats[v_i] THEN
            RAISE EXCEPTION 'Estoc insuficient de "%": en queden % i se''n demanen %',
                  v_nom, v_stock, p_quantitats[v_i] USING ERRCODE = 'P0001';
        END IF;

        INSERT INTO linies_comanda (comanda_id, producte_id, quantitat, preu_unitari, descompte)
        VALUES (p_comanda_id, p_productes[v_i], p_quantitats[v_i], v_preu, 0);

        UPDATE productes SET stock = stock - p_quantitats[v_i] WHERE id = p_productes[v_i];

        RAISE NOTICE 'Línia afegida: % x % a %', p_quantitats[v_i], v_nom, v_preu;
    END LOOP;

    -- 3. La comanda queda pagada
    UPDATE comandes SET estat = 'pagat' WHERE id = p_comanda_id;

    RAISE NOTICE 'Comanda % confirmada per un total de %', p_comanda_id, fn_total_comanda(p_comanda_id);
END;
$$;

La crida, amb el cas feliç i el cas de fallada:

-- 2 unitats d'oli (id 1) i 1 de matcha (id 15) per a la Lucía, gestionada per l'Óscar
CALL sp_confirmar_comanda(1, 4, 'targeta', 4.95, ARRAY[1, 15], ARRAY[2, 1]);

NOTICE:  Línia afegida: 2 x Oli d'oliva verge extra 500 ml a 12.50
NOTICE:  Línia afegida: 1 x Te verd matcha cerimonial 30 g a 22.00
NOTICE:  Comanda 21 confirmada per un total de 47.00
CALL

-- Ara demanant més espelmes (id 13, stock 0) de les que hi ha
CALL sp_confirmar_comanda(1, 4, 'targeta', 4.95, ARRAY[13], ARRAY[1]);

ERROR:  Estoc insuficient de "Espelmes de cera de soja (pack 2)": en queden 0 i se'n demanen 1
CONTEXT:  PL/pgSQL function sp_confirmar_comanda(...) line 34 at RAISE

I la comanda 22 no existeix. Aquesta és tota la lliçó de l'apartat: el RAISE EXCEPTION va avortar la transacció, i amb ella es va desfer la capçalera que ja s'havia inserit. L'atomicitat de 09-02 està garantida per construcció, no per la disciplina de qui crida. Un CALL fora d'una transacció explícita s'executa en la seva pròpia transacció implícita, així que el procediment és atòmic sense escriure BEGIN ni COMMIT.

Compara-ho amb el que tenies a 09-03: allà, l'aplicació havia d'obrir la transacció, executar les quatre sentències en l'ordre correcte, comprovar l'UPDATE N de l'estoc i decidir si confirmava. Quatre llocs on equivocar-se, multiplicats per cada aplicació que confirmi comandes. Aquí n'hi ha un.

(Aquest procediment modifica les dades. Si el proves, recarrega després l'script de 01-06 per tornar a les 20 files de comandes i als estocs originals.)

Quan un procediment sí que necessita `COMMIT`

El COMMIT explícit té un cas clar: el procés per lots. Un procediment que recalcula un milió de files i confirma cada mil evita mantenir una transacció d'hores —amb el cost en VACUUM i bloquejos que va explicar 09-01:

LOOP
    UPDATE ... WHERE ... LIMIT 1000;     -- un lot
    EXIT WHEN NOT FOUND;
    COMMIT;                              -- només possible en un PROCEDURE
END LOOP;

És exactament el que una funció no pot fer, i la raó per la qual els procediments es van afegir al motor.

Volatilitat i `SECURITY DEFINER`

Tota funció declara —explícitament o implícitament— quant se'n pot refiar el planificador:

Categoria	Promet	Exemples	Conseqüència
`IMMUTABLE`	Mateixos arguments → sempre mateix resultat. No llegeix taules	`lower(x)`, `x * 1.21`	Es pot precalcular i fer servir en un índex d'expressió
`STABLE`	Mateix resultat dins d'una mateixa sentència. Pot llegir taules, no escriure	`fn_total_comanda`, `now()`	S'avalua una vegada per sentència quan es pot
`VOLATILE`	Qualsevol cosa: pot retornar una cosa diferent a cada crida i escriure	`random()`, tot el que fa `INSERT`	S'avalua a cada fila, sempre. És el valor per omissió

Que VOLATILE sigui el valor per omissió és l'error silenciós més car de la lliçó. Una funció que només llegeix i no declara res s'avalua fila a fila i no es pot indexar, així que aquí es tanca la promesa de 08-02: un índex d'expressió com ara CREATE INDEX ix_prod_lower ON productes (lower(nom)); exigeix que la funció sigui IMMUTABLE. Si no ho és, PostgreSQL respon functions in index expression must be marked IMMUTABLE — perquè un índex desa resultats precalculats i només té sentit si aquests resultats no canvien.

Marca STABLE tota funció de només lectura i IMMUTABLE tota funció de càlcul pur. I no menteixis al planificador: declarar IMMUTABLE una cosa que llegeix una taula produeix resultats incorrectes difícils de diagnosticar.

SECURITY DEFINER. Per omissió una funció corre amb els permisos de qui la crida (SECURITY INVOKER). Amb SECURITY DEFINER corre amb els de qui la va crear, cosa que permet donar accés controlat a dades que l'usuari no pot llegir directament. És potent i és una superfície d'atac: mal escrita, amb un search_path manipulable, és una escalada de privilegis de manual. Els permisos, els rols i com blindar aquestes funcions són matèria d'11-03.

La discussió honesta: què posar-hi a dins i què no

Això és el més valuós de la lliçó, i on la majoria dels tutorials callen.

A favor	En contra
Una sola anada i tornada: confirmar una comanda és 1 crida en lloc de 6 viatges per la xarxa	Difícil de versionar: el codi viu al catàleg, no al teu repositori, tret que hi imposis disciplina
La lògica és al costat de les dades: sense transferir milers de files per processar-les fora	Difícil de provar: no hi ha cap depurador decent ni un ecosistema de testing comparable al del teu llenguatge
Atomicitat garantida per construcció, no per disciplina de qui crida	Lògica repartida en dos llocs: en depurar cal mirar l'aplicació i la base de dades
Reutilització entre diverses aplicacions i llenguatges contra la mateixa base	Portabilitat nul·la: PL/pgSQL, T-SQL i PL/SQL no s'assemblen. Migrar de motor és reescriure
Un únic punt de veritat per a una regla de negoci crítica	Escala amb el servidor de BD, que és la peça més cara i difícil de replicar
Pot restringir el que fa un usuari a un conjunt d'operacions	Els ORM no les fan servir bé: es converteixen en un camí paral·lel (11-05)

I el criteri, que és el que cal endur-se:

Posa-ho a la base de dades quan…	Deixa-ho a l'aplicació quan…
És una regla d'integritat que cap aplicació no ha de poder saltar-se	És lògica de presentació, de flux o d'experiència d'usuari
Requereix atomicitat sobre diverses taules	Requereix cridar serveis externs (passarel·la de pagament, correu, API)
Processa moltes files i treure-les fora seria absurd	Canvia sovint i necessita el cicle de desplegament de l'aplicació
Ho comparteixen diverses aplicacions amb la mateixa base	Només ho fa servir una aplicació
És manteniment de la mateixa base (neteges, agregats, particions)	Necessita biblioteques, concurrència o càlcul que SQL no fa bé

La postura equilibrada, i la que sosté la majoria dels equips avui: poc codi a la base de dades, i del bo. Restriccions i CHECK sempre; vistes i funcions de càlcul, sense problema; procediments per al que de debò exigeixi atomicitat o procés massiu. El que no funciona és cap dels dos extrems: ni una aplicació anèmica que només crida dos-cents procediments, ni una base de dades tractada com un magatzem ximple en què cap regla no es pot garantir.

Nota de dialecte: aquí és on més divergeixen els motors.

Motor Llenguatge Peculiaritat

PostgreSQL PL/pgSQL (i PL/Python, PL/Perl, PL/v8…) CREATE FUNCTION / CREATE PROCEDURE; CALL; cos entre $$

MySQL 8 SQL/PSM Cal DELIMITER // abans de crear el procediment, perquè el ; intern tallaria la sentència

SQL Server T-SQL CREATE PROCEDURE ... AS BEGIN ... END, EXEC, @variables, TRY...CATCH

Oracle PL/SQL L'antecessor de PL/pgSQL: s'assemblen molt, però els paquets (PACKAGE) no tenen equivalent

SQLite — No té procediments ni funcions emmagatzemades. Només funcions definides per l'aplicació en obrir la connexió

Motor	Llenguatge	Peculiaritat
PostgreSQL	PL/pgSQL (i PL/Python, PL/Perl, PL/v8…)	`CREATE FUNCTION` / `CREATE PROCEDURE`; `CALL`; cos entre `$$`
MySQL 8	SQL/PSM	Cal `DELIMITER //` abans de crear el procediment, perquè el `;` intern tallaria la sentència
SQL Server	T-SQL	`CREATE PROCEDURE ... AS BEGIN ... END`, `EXEC`, `@variables`, `TRY...CATCH`
Oracle	PL/SQL	L'antecessor de PL/pgSQL: s'assemblen molt, però els paquets (`PACKAGE`) no tenen equivalent
SQLite	—	No té procediments ni funcions emmagatzemades. Només funcions definides per l'aplicació en obrir la connexió

Errors habituals i consells

Cridar un procediment amb SELECT. No és una expressió: CALL sp_confirmar_comanda(...). I a l'inrevés, CALL sobre una funció dona error.
Esperar que un COMMIT dins d'una funció funcioni. No pot: cannot commit while a subtransaction is active o invalid transaction termination. Només els procediments, i només si el CALL no és dins d'una transacció explícita.
Deixar la volatilitat per omissió. Tot és VOLATILE si no dius res: es reavalua fila a fila i no serveix per a un índex d'expressió (functions in index expression must be marked IMMUTABLE). Marca STABLE el que només llegeix.
Cridar una funció escalar sobre milions de files. És una correlacionada disfressada: una execució per fila. Reescriu-la com a JOIN, vista o funció que retorni taula.
Donar a un paràmetre el mateix nom que a una columna. L'ambigüitat es resol a favor del paràmetre i el WHERE deixa de filtrar. Prefix p_ sempre.
Confondre el BEGIN de PL/pgSQL amb el d'una transacció. Delimita un bloc de codi. La transacció és la de qui crida.
Fer servir EXCEPTION WHEN OTHERS THEN NULL. Silencia tots els errors, inclosos els que mai no hauries d'haver silenciat. Captura excepcions concretes i torna a llançar el que no sàpigues tractar.
Escriure ELSEIF o ELSE IF. A PL/pgSQL és ELSIF.
Consell: desa el codi al repositori, en fitxers .sql amb CREATE OR REPLACE, i desplega'l amb les migracions. Una funció que només existeix en producció no existeix.
Consell: comença per LANGUAGE sql. Si no necessites decidir ni repetir, no facis servir PL/pgSQL: la versió SQL pura és més curta, més ràpida i el planificador la pot integrar en la consulta que la crida.
Consell: RAISE NOTICE és el teu depurador. No hi ha gaire més, i amb RAISE NOTICE 'v_stock = %', v_stock; als punts clau es resol el 90 % dels problemes.

Exercicis

Exercici 1

Escriu fn_puntuacio_mitjana(p_producte_id), que retorni la puntuació mitjana d'un producte arrodonida a dos decimals, o NULL si no té ressenyes. (1) Tria LANGUAGE sql o plpgsql i justifica-ho. (2) Declara la volatilitat correcta. (3) Fes-la servir en una consulta que llisti els 20 productes amb la seva mitjana, i comprova que l'oli (producte 1) surt amb 5.00, l'arròs amb 4.50 i onze productes amb NULL.

Exercici 2

Sobre sp_confirmar_comanda, respon raonant i comprova després. (1) Si el segon producte de la llista no té estoc, queda inserida la primera línia? I la capçalera de la comanda? Per què? (2) Què passaria si en traguessis el FOR UPDATE del SELECT sobre productes i dos clients confirmessin alhora l'última unitat de matcha? (3) El podries convertir en funció en lloc de procediment? Què hi perdries?

Exercici 3

L'equip discuteix on posar quatre regles de BotigaVerda. Decideix, amb el criteri de l'apartat 9, si van a la base de dades o a l'aplicació, i amb quina eina exacta:

"El preu de venda no pot ser mai negatiu."
"En confirmar una comanda cal enviar un correu de confirmació al client."
"Cada nit cal recalcular el rànquing de productes més venuts del mes."
"Un client no pot tenir més de tres comandes pendents alhora."

Solucions

Solució 1

CREATE OR REPLACE FUNCTION fn_puntuacio_mitjana(p_producte_id INTEGER)
RETURNS NUMERIC(3,2) LANGUAGE sql STABLE AS $$
    SELECT ROUND(AVG(r.puntuacio), 2) FROM ressenyes AS r WHERE r.producte_id = p_producte_id;
$$;

SELECT p.id, p.nom, fn_puntuacio_mitjana(p.id) AS mitjana
FROM   productes AS p ORDER BY mitjana DESC NULLS LAST, p.id;

id	nom	mitjana
1	Oli d'oliva verge extra 500 ml	5.00
15	Te verd matcha cerimonial 30 g	5.00
2	Arròs integral ecològic 1 kg	4.50
6	Crema facial d'àloe vera 50 ml	4.50

(4 primeres de 20 files; segueixen el detergent i el raspall amb 4.00, el tomàquet i les bosses amb 3.00, la kombutxa amb 2.00 i onze productes amb NULL.)

1. LANGUAGE sql: no hi ha cap decisió ni bucle, només una consulta. És més curta i el planificador la pot integrar en la consulta que la crida, cosa que amb plpgsql no passa. 2. STABLE: llegeix taules, així que no pot ser IMMUTABLE; i no escriu, així que declarar-la VOLATILE seria malbaratar optimitzacions. 3. L'AVG sobre un conjunt buit retorna NULL sense necessitat de cap IF — i aquí NULL és el correcte: "no hi ha opinions" no és el mateix que "zero estrelles" (04-03).

Solució 2

1. No en queda res: ni la línia, ni la capçalera. El RAISE EXCEPTION avorta la transacció sencera, i com que el CALL es va executar en la seva pròpia transacció implícita, es desfà tot el fet des del principi del procediment — inclosa la primera línia, que ja estava inserida, i l'UPDATE que ja n'havia descomptat l'estoc. És l'atomicitat de 09-02 aplicada sense que ningú hagi de recordar-se d'escriure ROLLBACK. Sí que queda consumit el valor de la seqüència de comandes.id, pel que va explicar 09-05: nextval no és transaccional.

2. Seria l'actualització perduda de 09-04, exactament. Sense FOR UPDATE, totes dues sessions llegirien stock = 1, totes dues passarien l'IF v_stock < quantitat, i totes dues inseririen la seva línia: es vendrien dues unitats d'una. El CHECK (stock >= 0) de la taula salvaria els mobles en aquest cas concret —el segon UPDATE fallaria en intentar deixar l'estoc en −1—, però això és sort, no disseny. Amb FOR UPDATE, la segona sessió espera, rellegeix stock = 0 i llança la seva excepció d'estoc insuficient, que és el missatge correcte per al client.

3. Sí, i funcionaria igual de bé en aquest cas, perquè tota la lògica cap en una sola transacció: n'hi hauria prou amb CREATE FUNCTION ... RETURNS INTEGER retornant l'id de la comanda, i executar-la amb SELECT. El que hi perdries és la capacitat de fer COMMIT intermedis, irrellevant aquí però decisiva si algun dia el procediment hagués de confirmar mil comandes per lots. S'hi guanya alguna cosa a canvi: una funció es pot fer servir dins d'una consulta. L'elecció honesta és la de l'apartat 1: això fa una feina, no calcula un valor, així que procediment.

Solució 3

#	Regla	On	Eina
1	Preu no negatiu	Base de dades	Un `CHECK (preu >= 0)` (ja és a l'esquema de 01-06). Ni funció ni procediment: és integritat pura i la restricció declarativa sempre guanya
2	Correu de confirmació	Aplicació	És una crida a un servei extern. Dins d'una transacció seria desastrós: si la transacció es desfà, el correu ja està enviat i no es pot desenviar (09-01)
3	Recalcular el rànquing cada nit	Base de dades	Una vista materialitzada amb `REFRESH` programat (10-01), o un procediment cridat des de `cron`. Processa moltes files i treure-les fora seria absurd
4	Màxim tres comandes pendents	Depèn	Un `CHECK` no pot (consulta una altra taula). Si és una regla inviolable, va a dins: disparador (10-05) o el mateix `sp_confirmar_comanda`. Si és una política comercial que canvia cada trimestre, millor a l'aplicació

La quarta és la interessant i no té resposta única: la pregunta correcta no és "es pot?" sinó "què passa si algú se la salta?". Si la resposta és "dades corruptes", va a la base de dades; si és "una experiència de compra estranya", va a l'aplicació.

Conclusió

Codi que viu a la base de dades, amb les seves dues cares:

Funció enfront de procediment: una funció retorna un valor, es crida amb SELECT, es pot fer servir dins d'una consulta i no controla transaccions; un procediment es crida amb CALL, no retorna res (tret que sigui per INOUT) i sí que pot fer COMMIT/ROLLBACK. Si retorna una dada, funció; si fa una feina, procediment.
LANGUAGE sql n'hi ha prou per encapsular una consulta amb paràmetres, i és la primera opció. PL/pgSQL hi afegeix DECLARE, IF/ELSIF, FOR ... IN SELECT, RETURN QUERY, RAISE NOTICE/EXCEPTION i el bloc EXCEPTION WHEN — amb l'avís que el seu BEGIN no és el d'una transacció.
RETURNS TABLE(...) converteix una funció en una taula parametritzada, que és el que una vista no pot ser. Si no hi ha paràmetres, vista; si n'hi ha, funció.
Els tres exemples: fn_total_comanda (escalar, amb els tiquets de 42,10, 64,88, 66,90 i 22,60 €), fn_vendes_per_categoria (taula, 603,52 € el 2025 i 124,43 € el 2026) i sp_confirmar_comanda, on viu per fi la lògica de 09-03: insereix, descompta estoc amb FOR UPDATE, llança RAISE EXCEPTION si en falta i deixa la base intacta si alguna cosa falla.
La volatilitat importa: VOLATILE és el valor per omissió i significa "reavalua'm a cada fila"; IMMUTABLE és requisit per a un índex d'expressió (08-02) i STABLE és el correcte per a gairebé tota funció de només lectura. SECURITY DEFINER dona poder i obre una superfície d'atac (11-03).
I la discussió honesta: una crida en lloc de sis viatges, lògica al costat de les dades, atomicitat garantida i reutilització, enfront de versionatge difícil, proves incòmodes, lògica en dos llocs, portabilitat nul·la i escalat lligat al servidor de BD. La postura sensata és poc codi a la base de dades, i del bo.

Queda una pregunta que aquest esquema no respon: i si la lògica s'ha d'executar sense que ningú la cridi? Un procediment cal invocar-lo, i n'hi ha prou que una aplicació escrigui directament a la taula per saltar-se'l sencer. A la lliçó següent, disparadors, veuràs codi que es dispara sol quan passa alguna cosa: BEFORE o AFTER d'un INSERT, UPDATE o DELETE, amb NEW i OLD, i amb la capacitat de modificar o cancel·lar l'operació en curs. Amb ell construiràs l'auditoria de canvis de preu, mantindràs al dia un total desnormalitzat, impediràs que un client ressenyi un producte que no ha comprat —la regla que 09-02 va deixar fora de l'abast d'un CHECK— i descomptaràs estoc automàticament. I en veuràs els perills amb el mateix detall, perquè un disparador és l'única peça del curs capaç de fer que un UPDATE faci alguna cosa que tu no vas escriure.

Procediments emmagatzemats

Contingut

Funció enfront de procediment

`CREATE FUNCTION` amb `LANGUAGE sql`

PL/pgSQL en una taula de referència

Paràmetres, tipus de retorn, sobrecàrrega i esborrat

`fn_total_comanda`: una funció escalar

`fn_vendes_per_categoria`: una funció que retorna taula

`sp_confirmar_comanda`: el procediment de 09-03

Quan un procediment sí que necessita `COMMIT`

Volatilitat i `SECURITY DEFINER`

La discussió honesta: què posar-hi a dins i què no

Errors habituals i consells

Exercicis

Exercici 1

Exercici 2

Exercici 3

Solucions

Conclusió

Curs de SQL

Mòdul 1: Introducció a SQL

Mòdul 2: Consultes bàsiques de SQL

Mòdul 3: Treballar amb múltiples taules

Mòdul 4: Filtratge avançat de dades

Mòdul 5: Manipulació de dades

Mòdul 6: Funcions avançades de SQL

Mòdul 7: Subconsultes i consultes imbricades

Mòdul 8: Índexs i optimització del rendiment

Mòdul 9: Transaccions i concurrència

Mòdul 10: Temes avançats

Mòdul 11: SQL a la pràctica

Mòdul 12: Projecte final

Procediments emmagatzemats

Contingut

Funció enfront de procediment

CREATE FUNCTION amb LANGUAGE sql

PL/pgSQL en una taula de referència

Paràmetres, tipus de retorn, sobrecàrrega i esborrat

fn_total_comanda: una funció escalar

fn_vendes_per_categoria: una funció que retorna taula

sp_confirmar_comanda: el procediment de 09-03

Quan un procediment sí que necessita COMMIT

Volatilitat i SECURITY DEFINER

La discussió honesta: què posar-hi a dins i què no

Errors habituals i consells

Exercicis

Exercici 1

Exercici 2

Exercici 3

Solucions

Conclusió

Curs de SQL

Mòdul 1: Introducció a SQL

Mòdul 2: Consultes bàsiques de SQL

Mòdul 3: Treballar amb múltiples taules

Mòdul 4: Filtratge avançat de dades

Mòdul 5: Manipulació de dades

Mòdul 6: Funcions avançades de SQL

Mòdul 7: Subconsultes i consultes imbricades

Mòdul 8: Índexs i optimització del rendiment

Mòdul 9: Transaccions i concurrència

Mòdul 10: Temes avançats

Mòdul 11: SQL a la pràctica

Mòdul 12: Projecte final

`CREATE FUNCTION` amb `LANGUAGE sql`

`fn_total_comanda`: una funció escalar

`fn_vendes_per_categoria`: una funció que retorna taula

`sp_confirmar_comanda`: el procediment de 09-03

Quan un procediment sí que necessita `COMMIT`

Volatilitat i `SECURITY DEFINER`