39. PostgreSQL: Routines and triggers (PL/pgSQL)

CREATE [OR REPLACE] FUNCTION [schema.]name ( [parameter [, …]] )
  RETURNS {type | SETOF type | SETOF table | TABLE (column type [, …])}
  LANGUAGE {plpgsql | sql}
  [IMMUTABLE | STABLE | VOLATILE]
  [STRICT | RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT]
  [SECURITY {INVOKER | DEFINER}]
  AS $$ body $$;

CREATE FUNCTION prix_ttc(prix numeric(10,2), taux numeric(4,2) DEFAULT 0.20)
RETURNS numeric(10,2) LANGUAGE plpgsql IMMUTABLE AS $$
DECLARE
  resultat numeric(10,2);
BEGIN
  resultat := prix * (1 + taux);
  RETURN round(resultat, 2);
END;
$$;

SELECT prix_ttc(10), prix_ttc(10, 0.055);
SELECT code, prix, prix_ttc(prix) AS ttc FROM article ORDER BY id;

 prix_ttc(10) | prix_ttc(10, 0.055)
--------------+---------------------
        12.00 |               10.60

  code  | prix | ttc
--------+------+------
 STY-01 | 1.20 | 1.44
 CAH-96 | 2.50 | 3.00
 CLA-A4 | 4.90 | 5.88

CREATE FUNCTION libelle_article(p_id integer) RETURNS text LANGUAGE sql STABLE AS $$
  SELECT code || ' - ' || libelle FROM article WHERE id = p_id
$$;

CREATE FUNCTION double(integer) RETURNS integer LANGUAGE sql AS 'SELECT $1 * 2';
CREATE FUNCTION triple(x integer) RETURNS integer LANGUAGE sql RETURN x * 3;

SELECT libelle_article(1), libelle_article(99), double(21), triple(7);

  libelle_article(1)  | libelle_article(99) | double(21) | triple(7)
----------------------+---------------------+------------+-----------
 STY-01 - Stylo bille |                     |         42 |        21

CREATE FUNCTION total_commande(p_commande integer) RETURNS numeric(12,2)
LANGUAGE plpgsql STABLE AS $$
DECLARE
  v_total numeric(12,2);
BEGIN
  SELECT sum(quantite * prix_unit) INTO v_total
    FROM ligne_commande WHERE commande_id = p_commande;
  RETURN coalesce(v_total, 0);
END;
$$;

SELECT total_commande(1), total_commande(42);

 total_commande(1) | total_commande(42)
-------------------+--------------------
             22.00 |               0.00

CREATE TABLE remise (taux numeric(5,2), depuis date);
INSERT INTO remise VALUES (0.05, '2026-01-01'), (0.10, '2026-09-01');

CREATE FUNCTION remise_courante() RETURNS numeric(5,2) LANGUAGE sql STABLE AS $$
  SELECT taux FROM remise ORDER BY depuis DESC LIMIT 1
$$;

CREATE TABLE devis (
    id       serial PRIMARY KEY,
    client   integer NOT NULL,
    remise   numeric(5,2) DEFAULT remise_courante(),
    plafond  numeric(12,2) DEFAULT (remise_courante() * 1000)
);
INSERT INTO devis (client) VALUES (1), (2);   -- discount read from the remise table, for each row
SELECT * FROM devis;

 id | client | remise | plafond
----+--------+--------+---------
  1 |      1 |   0.10 |  100.00
  2 |      2 |   0.10 |  100.00

CREATE FUNCTION f_out(a integer, OUT carre integer) LANGUAGE plpgsql AS $$
BEGIN
  carre := a * a;
END $$;

SELECT f_out(5);      -- 25

CREATE FUNCTION articles_sous(p_prix numeric) RETURNS SETOF article LANGUAGE sql STABLE AS $$
  SELECT * FROM article WHERE prix < p_prix ORDER BY prix
$$;

SELECT id, code, prix FROM articles_sous(3);

 id |  code  | prix
----+--------+------
  1 | STY-01 | 1.20
  2 | CAH-96 | 2.50

CREATE FUNCTION commandes_client(p_client integer)
RETURNS TABLE (commande integer, passee date, montant numeric(12,2))
LANGUAGE plpgsql STABLE AS $$
BEGIN
  RETURN QUERY
    SELECT o.id, o.passee_le, sum(l.quantite * l.prix_unit)
      FROM commande o JOIN ligne_commande l ON l.commande_id = o.id
     WHERE o.client_id = p_client
     GROUP BY o.id, o.passee_le
     ORDER BY o.id;
END $$;

SELECT * FROM commandes_client(1);
SELECT * FROM commandes_client(1) WHERE montant > 10;

 commande |   passee   | montant
----------+------------+---------
        1 | 2026-10-01 |   22.00
        2 | 2026-10-05 |    9.80

 commande |   passee   | montant
----------+------------+---------
        1 | 2026-10-01 |   22.00

CREATE FUNCTION paliers(n integer) RETURNS SETOF integer LANGUAGE plpgsql AS $$
BEGIN
  FOR i IN 1 .. n LOOP
    IF i % 2 = 0 THEN
      RETURN NEXT i * 10;
    END IF;
  END LOOP;
  RETURN;
END $$;

CREATE FUNCTION carres(n integer, OUT k integer, OUT carre integer) RETURNS SETOF record
LANGUAGE plpgsql AS $$
BEGIN
  FOR i IN 1 .. n LOOP
    k := i;
    carre := i * i;
    RETURN NEXT;
  END LOOP;
END $$;

SELECT * FROM paliers(7);
SELECT x.p * 2 FROM paliers(5) AS x(p);
SELECT * FROM carres(3);

 paliers
---------
      20
      40
      60

 x.p * 2
---------
      40
      80

 k | carre
---+-------
 1 |     1
 2 |     4
 3 |     9

DROP FUNCTION [IF EXISTS] [schema.]name [(types)] [CASCADE | RESTRICT];

CREATE [OR REPLACE] PROCEDURE [schema.]name ( [parameter [, …]] )
  LANGUAGE {plpgsql | sql}
  [SECURITY {INVOKER | DEFINER}]
  AS $$ body $$;

CALL [schema.]name ( [arguments] );
DROP PROCEDURE [IF EXISTS] [schema.]name [(types)];

CREATE PROCEDURE passer_commande(p_client integer, p_article integer, p_qte integer,
                                 INOUT p_commande integer DEFAULT NULL)
LANGUAGE plpgsql AS $$
DECLARE
  v_prix  numeric(10,2);
  v_stock integer;
BEGIN
  SELECT prix, stock INTO v_prix, v_stock FROM article WHERE id = p_article;
  IF NOT FOUND THEN
    RAISE EXCEPTION 'article % inconnu', p_article USING ERRCODE = 'no_data_found';
  END IF;
  IF v_stock < p_qte THEN
    RAISE EXCEPTION 'stock insuffisant pour l''article % : % demandé, % disponible',
                    p_article, p_qte, v_stock
      USING ERRCODE = 'P0100';
  END IF;
  INSERT INTO commande (client_id) VALUES (p_client)
    RETURNING id INTO p_commande;
  INSERT INTO ligne_commande VALUES (p_commande, p_article, p_qte, v_prix);
  UPDATE article SET stock = stock - p_qte WHERE id = p_article;
  RAISE NOTICE 'commande % créée pour le client %', p_commande, p_client;
END;
$$;

CALL passer_commande(2, 2, 5);
CALL passer_commande(2, 3, 500);
CALL passer_commande(2, 99, 1);

NOTICE:  commande 4 créée pour le client 2
 p_commande
------------
          4
CALL
ERROR:  stock insuffisant pour l'article 3 : 500 demandé, 80 disponible   (P0100)
ERROR:  article 99 inconnu                                                 (P0002)

BEGIN;
CALL passer_commande(3, 1, 1);     -- NOTICE:  commande 7 créée pour le client 3
ROLLBACK;                           -- the order, its line and the stock are undone
SELECT count(*) FROM commande WHERE client_id = 3;     -- 0

CREATE PROCEDURE p_ecrit_echoue() LANGUAGE plpgsql AS $$
BEGIN
  UPDATE client SET ville = 'Avant-erreur' WHERE id = 3;
  RAISE EXCEPTION 'stop';
END $$;

CALL p_ecrit_echoue();                          -- ERROR:  stop   (P0001)
SELECT ville FROM client WHERE id = 3;          -- Lyon: the UPDATE is undone

cur.execute("CALL passer_commande(%s, %s, %s)", (1, 1, 1))
print(cur.fetchone())            # (6,)  : the value of the INOUT parameter
print(conn.notices)              # ['NOTICE:  commande 6 créée pour le client 1\n']

cur.execute("SELECT prix_ttc(%s)", (10,))
print(cur.fetchone())            # (Decimal('12.00'),)

cur.callproc("prix_ttc", (10,))
print(cur.fetchone())            # (Decimal('12.00'),)
cur.callproc("paliers", (7,))
print(cur.fetchall())            # [(20,), (40,), (60,)]

CREATE FUNCTION remise(montant numeric, taux numeric DEFAULT 0.1) RETURNS numeric
    AS $$ SELECT montant * (1 - taux) $$ LANGUAGE sql;

SELECT remise(100), remise(100, taux => 0.2), remise(taux => 0.5, montant => 100);

      remise(100)      | remise(100, taux => 0.2) | remise(taux => 0.5, montant => 100)
-----------------------+--------------------------+-------------------------------------
 90.000000000000000000 |    80.000000000000000000 |               50.000000000000000000

CREATE TABLE t (i int);
DO $$
DECLARE n int;
BEGIN
  INSERT INTO t SELECT i FROM generate_series(1, 3) AS g(i);
  SELECT count(*) INTO n FROM t;
  RAISE NOTICE '% lignes', n;
END $$;

DO $$ BEGIN INSERT INTO t VALUES (4); RAISE EXCEPTION 'non'; END $$;
SELECT count(*) FROM t;

CREATE TABLE
NOTICE:  3 lignes
DO
ERROR:  non   (P0001)
 count(*)
----------
        3

[<<label>>]
[DECLARE
   declarations]
BEGIN
   statements
[EXCEPTION
   WHEN condition THEN statements …]
END [label];

DECLARE
  v_total   numeric(12,2);                       -- NULL at the start
  v_compte  integer := 0;                        -- or DEFAULT 0, or = 0
  c_sep     CONSTANT text := ' : ';
  v_nn      integer NOT NULL := 0;
  v_prix    article.prix%TYPE;                   -- type of a column
  v_copie   v_prix%TYPE;                         -- type of another variable
  a         article%ROWTYPE;                     -- a row of the table: a.code, a.prix…
  r         RECORD;                              -- row of a query, in a FOR loop
  p         ALIAS FOR $1;                        -- name given to an anonymous parameter
  c_art     CURSOR FOR SELECT code FROM article ORDER BY id;

v_total := v_total + 1;        -- or v_total = v_total + 1
a.stock := 0;                  -- field of a record
NEW.modifie_le := now();       -- column of a trigger's row

CREATE FUNCTION categorie_prix(p numeric) RETURNS text LANGUAGE plpgsql AS $$
BEGIN
  IF p IS NULL THEN
    RETURN 'inconnu';
  ELSIF p < 2 THEN
    RETURN 'petit';
  ELSIF p < 4 THEN
    RETURN 'moyen';
  ELSE
    RETURN 'grand';
  END IF;
END $$;

SELECT code, categorie_prix(prix) FROM article ORDER BY id;

  code  | categorie_prix(prix)
--------+----------------------
 STY-01 | petit
 CAH-96 | moyen
 CLA-A4 | grand

CASE n
  WHEN 1, 7 THEN r := 'week-end';
  WHEN 2, 3, 4, 5, 6 THEN r := 'semaine';
  ELSE r := 'invalide';
END CASE;

CASE WHEN n > 100 THEN r := r || '!'; ELSE NULL; END CASE;

CREATE FUNCTION boucles(n integer) RETURNS text LANGUAGE plpgsql AS $$
DECLARE
  s integer := 0;
  i integer := 0;
  t text := '';
BEGIN
  FOR j IN 1 .. n LOOP s := s + j; END LOOP;
  FOR j IN REVERSE n .. 1 BY 2 LOOP t := t || j || ' '; END LOOP;
  WHILE i < 3 LOOP
    i := i + 1;
    CONTINUE WHEN i = 2;
    t := t || 'w' || i || ' ';
  END LOOP;
  <<dehors>>
  LOOP
    i := i + 1;
    LOOP
      EXIT dehors WHEN i >= 6;
      EXIT;
    END LOOP;
  END LOOP dehors;
  RETURN s || ' / ' || t || '/ ' || i;
END $$;

SELECT boucles(5);       -- 15 / 5 3 1 w1 w3 / 6

CREATE FUNCTION liste_codes(p_prix_min numeric) RETURNS text LANGUAGE plpgsql AS $$
DECLARE
  r RECORD;
  res text := '';
BEGIN
  FOR r IN SELECT code, prix FROM article WHERE prix >= p_prix_min ORDER BY code LOOP
    res := res || r.code || '=' || r.prix || ';';
  END LOOP;
  IF NOT FOUND THEN res := 'aucun'; END IF;
  RETURN res;
END $$;

SELECT liste_codes(2), liste_codes(100);     -- CAH-96=2.50;CLA-A4=4.90; | aucun

SELECT prix, stock INTO v_prix, v_stock FROM article WHERE id = p_article;
SELECT * INTO a FROM article WHERE id = p_id;            -- a article%ROWTYPE
SELECT nom INTO STRICT v FROM client WHERE ville = p_ville;

UPDATE article SET stock = stock WHERE prix > 2;
GET DIAGNOSTICS n = ROW_COUNT;          -- 2
PERFORM 1 FROM client WHERE ville = 'Lyon';
IF FOUND THEN … END IF;

CREATE FUNCTION codes() RETURNS text LANGUAGE plpgsql AS $$
DECLARE
  c1  CURSOR FOR SELECT code FROM article ORDER BY id;
  v   text;
  res text := '';
BEGIN
  OPEN c1;
  LOOP
    FETCH c1 INTO v;
    EXIT WHEN NOT FOUND;
    res := res || v || ' ';
  END LOOP;
  CLOSE c1;
  RETURN res;
END $$;

SELECT codes();         -- STY-01 CAH-96 CLA-A4

DO $$
BEGIN
  RAISE NOTICE 'a % b %% c %', 1, NULL;
  RAISE WARNING 'stock bas pour %', 'CLA-A4';
  RAISE INFO 'étape % terminée', 2;
  RAISE DEBUG 'non transmis';
END $$;

NOTICE:  a 1 b % c <NULL>
WARNING:  stock bas pour CLA-A4
INFO:  étape 2 terminée
DO

RAISE EXCEPTION 'erreur simple %', 42;                                    -- P0001
RAISE 'forme courte %', 1;                                                -- P0001
RAISE EXCEPTION 'paramètre invalide' USING ERRCODE = '22023';             -- 22023
RAISE EXCEPTION 'article % inconnu', p USING ERRCODE = 'no_data_found';   -- P0002
RAISE unique_violation USING MESSAGE = 'doublon métier';                  -- 23505
RAISE SQLSTATE 'P0042';                                                   -- P0042, message "P0042"

CREATE FUNCTION ajoute_article(p_code text, p_libelle text, p_prix numeric)
RETURNS text LANGUAGE plpgsql AS $$
DECLARE
  v_msg  text;
  v_etat text;
BEGIN
  INSERT INTO article (code, libelle, prix) VALUES (p_code, p_libelle, p_prix);
  RETURN 'créé';
EXCEPTION
  WHEN unique_violation THEN
    RETURN 'existe déjà';
  WHEN not_null_violation OR check_violation THEN
    GET STACKED DIAGNOSTICS v_msg = MESSAGE_TEXT, v_etat = RETURNED_SQLSTATE;
    RETURN 'refusé (' || v_etat || ') : ' || v_msg;
  WHEN OTHERS THEN
    RETURN 'autre : ' || SQLSTATE;
END $$;

SELECT ajoute_article('GOM-04', 'Gomme', 0.80);      -- créé
SELECT ajoute_article('GOM-04', 'Gomme', 0.80);      -- existe déjà
SELECT ajoute_article('X', NULL, 1);                 -- refusé (23502) : Column 'libelle' cannot be null
SELECT ajoute_article('Y', 'Prix négatif', -1);
-- refusé (23514) : new row for relation "article" violates check constraint "CONSTRAINT_1"
SELECT ajoute_article('GOM-06', 'Gomme', 99999999999);   -- autre : 22003

CREATE FUNCTION division(a integer) RETURNS integer LANGUAGE plpgsql AS $$
BEGIN
  RETURN 10 / a;
EXCEPTION WHEN division_by_zero THEN
  RAISE NOTICE 'journalisé : % (%)', SQLERRM, SQLSTATE;
  RETURN NULL;
END $$;

SELECT division(0);

NOTICE:  journalisé : division by zero (22012)
 division(0)
-------------

CREATE TABLE trace (id int, texte text);
DO $$
BEGIN
  INSERT INTO trace VALUES (1, 'gardé');
  BEGIN
    INSERT INTO trace VALUES (2, 'défait');
    RAISE EXCEPTION 'stop';
  EXCEPTION WHEN OTHERS THEN
    INSERT INTO trace VALUES (3, 'gardé aussi');
  END;
END $$;
SELECT * FROM trace ORDER BY id;

 id |    texte
----+-------------
  1 | gardé
  3 | gardé aussi

CREATE [OR REPLACE] TRIGGER name
  {BEFORE | AFTER} {INSERT | UPDATE [OF column [, …]] | DELETE} [OR …]
  ON table
  FOR EACH ROW
  [WHEN (condition)]
  EXECUTE {FUNCTION | PROCEDURE} function ( [arguments] );

DROP TRIGGER [IF EXISTS] name ON table [CASCADE | RESTRICT];

ALTER TABLE client ADD COLUMN modifie_le timestamp;

CREATE FUNCTION horodate() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
  NEW.modifie_le := now();
  RETURN NEW;
END $$;

CREATE TRIGGER client_horodate BEFORE INSERT OR UPDATE ON client
  FOR EACH ROW EXECUTE FUNCTION horodate();

UPDATE client SET ville = 'Lyon' WHERE id = 1;
SELECT id, ville, modifie_le IS NOT NULL AS horodate FROM client ORDER BY id;

 id |    ville    | horodate
----+-------------+----------
  1 | Lyon        | t
  2 | Constantine | f
  3 | Lyon        | f

CREATE FUNCTION controle_prix() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
  IF NEW.prix <= 0 THEN
    RAISE EXCEPTION 'prix invalide pour % : %', NEW.code, NEW.prix
      USING ERRCODE = 'check_violation';
  END IF;
  RETURN NEW;
END $$;

CREATE TRIGGER article_prix BEFORE INSERT OR UPDATE OF prix ON article
  FOR EACH ROW EXECUTE FUNCTION controle_prix();

UPDATE article SET prix = 0 WHERE id = 1;
-- ERROR:  prix invalide pour STY-01 : 0.00   (23514)

CREATE TABLE journal (id serial PRIMARY KEY, quand timestamp NOT NULL DEFAULT now(),
                      table_nom text, operation text, cle integer, detail text);

CREATE FUNCTION journalise() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
  IF TG_OP = 'INSERT' THEN
    INSERT INTO journal (table_nom, operation, cle, detail)
      VALUES (TG_TABLE_NAME, TG_OP, NEW.id, 'stock ' || NEW.stock);
    RETURN NEW;
  ELSIF TG_OP = 'UPDATE' THEN
    INSERT INTO journal (table_nom, operation, cle, detail)
      VALUES (TG_TABLE_NAME, TG_OP, NEW.id, 'stock ' || OLD.stock || ' -> ' || NEW.stock);
    RETURN NEW;
  ELSE
    INSERT INTO journal (table_nom, operation, cle, detail)
      VALUES (TG_TABLE_NAME, TG_OP, OLD.id, 'supprimé');
    RETURN OLD;
  END IF;
END $$;

CREATE TRIGGER article_journal AFTER INSERT OR UPDATE OR DELETE ON article
  FOR EACH ROW EXECUTE FUNCTION journalise();

INSERT INTO article (code, libelle, prix, stock) VALUES ('GOM-05', 'Gomme', 0.80, 50);
UPDATE article SET stock = stock - 5 WHERE code = 'GOM-05';
DELETE FROM article WHERE code = 'GOM-05';
SELECT table_nom, operation, detail FROM journal ORDER BY id;

 table_nom | operation |     detail
-----------+-----------+----------------
 article   | INSERT    | stock 50
 article   | UPDATE    | stock 50 -> 45
 article   | DELETE    | supprimé

CREATE FUNCTION ignore_petites() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
  IF NEW.quantite <= 1 THEN RETURN NULL; END IF;     -- row skipped, no error
  RETURN NEW;
END $$;

CREATE TRIGGER ligne_filtre BEFORE INSERT ON ligne_commande
  FOR EACH ROW EXECUTE FUNCTION ignore_petites();

INSERT INTO ligne_commande VALUES (3, 1, 1, 1.20), (3, 3, 2, 4.90);    -- INSERT 0 1

CREATE FUNCTION maj_stock() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
  UPDATE article SET stock = stock - NEW.quantite WHERE id = NEW.article_id;
  RETURN NULL;
END $$;

CREATE TRIGGER ligne_stock AFTER INSERT ON ligne_commande
  FOR EACH ROW EXECUTE FUNCTION maj_stock();

SELECT stock FROM article WHERE id = 2;                -- 195
INSERT INTO ligne_commande VALUES (2, 2, 10, 2.50);
SELECT stock FROM article WHERE id = 2;                -- 185

-- only when the stock changes and falls below 5
CREATE TRIGGER quand_stock AFTER UPDATE ON article FOR EACH ROW
  WHEN (OLD.stock IS DISTINCT FROM NEW.stock AND NEW.stock < 5)
  EXECUTE FUNCTION journalise();

-- arguments read by TG_ARGV[0], TG_ARGV[1]; TG_NARGS is 2
CREATE FUNCTION avec_args() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
  RAISE NOTICE '% % : % arguments, % et %', TG_NAME, TG_WHEN || '/' || TG_LEVEL || '/' || TG_OP,
               TG_NARGS, TG_ARGV[0], TG_ARGV[1];
  RAISE NOTICE 'table %.%', TG_TABLE_SCHEMA, TG_TABLE_NAME;
  RETURN NEW;
END $$;

CREATE TRIGGER cmd_args BEFORE UPDATE ON commande FOR EACH ROW
  EXECUTE FUNCTION avec_args('alerte', 7);

UPDATE commande SET etat = 'livrée' WHERE id = 1;

NOTICE:  cmd_args BEFORE/ROW/UPDATE : 2 arguments, alerte et 7
NOTICE:  table ventes.commande
UPDATE 1

SELECT proname, prokind, pg_get_function_arguments(oid) AS args,
       pg_get_function_result(oid) AS res
  FROM pg_proc WHERE proname IN ('prix_ttc', 'passer_commande', 'f_out') ORDER BY proname;

     proname     | prokind |                                           args                                            |      res
-----------------+---------+-------------------------------------------------------------------------------------------+---------------
 f_out           | f       | a integer, OUT carre integer                                                              | integer
 passer_commande | p       | p_client integer, p_article integer, p_qte integer, INOUT p_commande integer DEFAULT NULL |
 prix_ttc        | f       | prix numeric(10,2), taux numeric(4,2) DEFAULT 0.20                                        | numeric(10,2)

SELECT proname, provolatile, proisstrict, prosecdef FROM pg_proc
 WHERE proname IN ('prix_ttc', 'f_strict') ORDER BY 1;
SELECT pg_get_functiondef('prix_ttc'::regproc);
SELECT pg_get_triggerdef(oid) FROM pg_trigger WHERE tgname = 'article_prix';

 proname  | provolatile | proisstrict | prosecdef
----------+-------------+-------------+-----------
 f_strict | v           | t           | f
 prix_ttc | i           | f           | f

CREATE OR REPLACE FUNCTION ventes.prix_ttc(prix numeric(10,2), taux numeric(4,2) DEFAULT 0.20)
 RETURNS numeric(10,2)
 LANGUAGE plpgsql
 IMMUTABLE
AS $function$
DECLARE
  resultat numeric(10,2);
BEGIN
  resultat := prix * (1 + taux);
  RETURN round(resultat, 2);
END;
$function$

CREATE TRIGGER article_prix BEFORE INSERT OR UPDATE OF prix ON ventes.article FOR EACH ROW EXECUTE FUNCTION controle_prix()

SELECT routine_name, routine_type, data_type FROM information_schema.routines
 WHERE routine_schema = 'ventes' AND routine_name IN ('prix_ttc', 'passer_commande', 'horodate')
 ORDER BY 1;
SELECT trigger_name, event_manipulation, action_timing, action_statement, action_condition
  FROM information_schema.triggers WHERE event_object_table = 'article'
 ORDER BY trigger_name, event_manipulation;

  routine_name   | routine_type | data_type
-----------------+--------------+-----------
 horodate        | FUNCTION     | trigger
 passer_commande | PROCEDURE    |
 prix_ttc        | FUNCTION     | numeric

  trigger_name   | event_manipulation | action_timing |         action_statement         |                    action_condition
-----------------+--------------------+---------------+----------------------------------+--------------------------------------------------------
 article_journal | DELETE             | AFTER         | EXECUTE FUNCTION journalise()    |
 article_journal | INSERT             | AFTER         | EXECUTE FUNCTION journalise()    |
 article_journal | UPDATE             | AFTER         | EXECUTE FUNCTION journalise()    |
 article_prix    | INSERT             | BEFORE        | EXECUTE FUNCTION controle_prix() |
 article_prix    | UPDATE             | BEFORE        | EXECUTE FUNCTION controle_prix() |
 quand_stock     | UPDATE             | AFTER         | EXECUTE FUNCTION journalise()    | OLD.stock IS DISTINCT FROM NEW.stock AND NEW.stock < 5

-- as root
CREATE USER vendeur IDENTIFIED BY 'vendeur123';
CREATE FUNCTION nb_clients() RETURNS bigint LANGUAGE sql STABLE AS $$ SELECT count(*) FROM client $$;
CREATE FUNCTION nb_clients_def() RETURNS bigint LANGUAGE sql STABLE SECURITY DEFINER
    AS $$ SELECT count(*) FROM client $$;
GRANT EXECUTE ON FUNCTION ventes.nb_clients_def() TO vendeur;
GRANT INSERT, SELECT ON ventes.article TO vendeur;

-- connected as vendeur
SELECT nb_clients();
SELECT nb_clients_def();
CREATE FUNCTION f_v() RETURNS int LANGUAGE sql AS 'SELECT 1';
INSERT INTO article (code, libelle, prix, stock) VALUES ('REG-30', 'Règle', 1.90, 10);
SELECT * FROM journal;

ERROR:  permission denied for function nb_clients   (42501)
 nb_clients_def()
------------------
                3
ERROR:  permission denied for schema ventes   (42501)
INSERT 0 1
ERROR:  permission denied for table journal   (42501)