PGSQL Toate comenzile și funcțiile PostgreSQL

`CREATE PROCEDURE și CALL` în PostgreSQL

Funcții, proceduri și triggere PostgreSQL 11+

O procedură este ca o funcție, dar se apelează cu CALL și poate face COMMIT sau ROLLBACK în interior, de exemplu pentru prelucrări pe loturi.

Sintaxă SQL

CREATE [OR REPLACE] PROCEDURE nume(param tip, ... [, INOUT param tip])
LANGUAGE plpgsql
AS $$
BEGIN
    ...
    COMMIT;
END
$$;

CALL nume(argumente);

Ce face `CREATE PROCEDURE și CALL`

Procedurile au apărut în PostgreSQL 11. Diferența principală față de funcții: o procedură apelată cu CALL (în afara unui bloc BEGIN) își poate controla tranzacțiile. Poate confirma lucrul făcut până la un punct cu COMMIT și continua cu o tranzacție nouă.

Asta contează la operații mari: arhivarea a milioane de rânduri în loturi de 10.000, cu COMMIT după fiecare lot, ține tranzacțiile scurte și nu blochează tabelul ore întregi.

Procedurile nu au RETURNS. Pot întoarce valori prin parametri INOUT (sau OUT, din PostgreSQL 14), care apar ca un rând de rezultat după CALL.

Parametri

Parametru	Descriere
`CALL nume(...)`	Rulează procedura.
`INOUT param`	Parametru care se întoarce în rezultatul lui `CALL`.
`COMMIT / ROLLBACK`	Permise doar când `CALL` nu rulează într-o tranzacție deschisă explicit.

Ce returnează

CREATE PROCEDURE la creare; CALL la apel (sau un rând cu parametrii INOUT).

Exemple

Exemplul 1: Arhivare pe loturi cu COMMIT

CREATE PROCEDURE arhiveaza_comenzi(p_zile integer, p_lot integer DEFAULT 10000)
LANGUAGE plpgsql
AS $$
DECLARE
    mutate integer;
BEGIN
    LOOP
        WITH lot AS (
            DELETE FROM comenzi
            WHERE id IN (SELECT id FROM comenzi
                         WHERE creat_la < now() - make_interval(days => p_zile)
                         LIMIT p_lot)
            RETURNING *
        )
        INSERT INTO comenzi_arhiva SELECT * FROM lot;

        GET DIAGNOSTICS mutate = ROW_COUNT;
        COMMIT;
        RAISE NOTICE 'Arhivate: %', mutate;
        EXIT WHEN mutate < p_lot;
    END LOOP;
END
$$;

CALL arhiveaza_comenzi(365);

Rezultat în psql

CREATE PROCEDURE
NOTICE:  Arhivate: 10000
NOTICE:  Arhivate: 10000
NOTICE:  Arhivate: 4318
CALL

Exemplul 2: Valoare întoarsă printr-un parametru INOUT

CREATE PROCEDURE numara_clienti(p_oras text, INOUT total integer DEFAULT NULL)
LANGUAGE plpgsql
AS $$
BEGIN
    SELECT count(*) INTO total FROM clienti WHERE oras = p_oras;
END
$$;

CALL numara_clienti('Iași', NULL);