29. Langage PostgreSQL

Une instance démarrée avec l'élément POSTGRESQL de sql_mode (voir 15.10) parle le protocole et la grammaire de PostgreSQL : les requêtes de psql, de psycopg2, de Npgsql ou de pgjdbc s'écrivent comme pour un serveur PostgreSQL. Ce chapitre liste ce que cette grammaire accepte, les extensions de MIRAJ qui y restent disponibles, la syntaxe propre à MIRAJ qui y est refusée et ce qui n'est pas encore pris en charge.

Le moteur, lui, est le même : une requête PostgreSQL est ramenée aux instructions de MIRAJ, avec leurs règles (types, conversions, collations).

29.1 Lexique#

Un identifiant entre guillemets reste insensible à la casse : "Articles" et articles désignent la même table (les noms de MIRAJ sont tous insensibles à la casse). Le nom écrit est gardé tel quel à la création.

29.2 Expressions et requêtes#

Calculs et conversions suivent le serveur de référence, et non le langage MIRAJ :

• une division ou un modulo par zéro (1 / 0, 5 % 0, x / 0.0) est une erreur 22012 (division_by_zero, que EXCEPTION WHEN division_by_zero attrape), pas un NULL ;
• un texte converti en nombre ('abc'::int, CAST('1.5' AS integer), 'x'::numeric) qui n'en est pas un donne 22P02 au lieu de son préfixe numérique ; un entier peut s'écrire 0x1F, 0o17, 0b101, 1_000 ; 'Infinity', '-Infinity' et 'NaN' sont des float8 ;
• un entier écrit qui tient sur 32 bits est un integer (pg_typeof(1), type annoncé au client) ;
• sum d'un integer est un bigint, sum d'un bigint un numeric (sans dépassement) ; avg d'entiers ou de décimaux est un numeric à 16 décimales au moins (2.3333333333333333) ;
• stddev et variance sont l'écart-type et la variance corrigés (stddev_samp, var_samp) ; avec stddev_pop et var_pop, ils rendent sur des entiers ou des décimaux un numeric exact à 16 décimales au moins (stddev de 1 à 5 : 1.5811388300841897), un double precision sur des flottants, et s'emploient aussi avec OVER ;
• x ^ y et power(x, y) rendent un double precision entre entiers ou avec un flottant, un numeric exact dès qu'un opérande est un décimal (2 ^ 0.5 : 1.4142135623730950), à 16 chiffres significatifs et pas moins de décimales qu'un opérande ; zéro à une puissance négative ou un négatif à une puissance non entière : 2201F ;
• lower(1), upper(1.5) : 42883, ces fonctions n'existant que pour le texte.

Valeurs écrites dans une colonne (INSERT, UPDATE) : une colonne boolean lit 't', 'yes', 'on', '1', leurs contraires et préfixes (22P02 pour 'maybe') ; une colonne uuid garde la forme canonique en minuscules (22P02 pour un texte qui n'est pas un UUID) ; une colonne json ou jsonb refuse un document illisible (22P02).

Clauses de SELECT :

• LIMIT n, LIMIT ALL, OFFSET n [ROWS] (seul ou avant LIMIT), FETCH {FIRST | NEXT} [n] {ROW | ROWS} ONLY ;
• ORDER BY x [ASC | DESC] [NULLS FIRST | NULLS LAST] (MIRAJ classe les NULL en tête dans l'ordre croissant ; l'ordre demandé est obtenu par une clé de tri ajoutée), x pouvant être un alias de la liste ; une colonne jsonb se trie dans l'ordre de PostgreSQL (null < chaînes < nombres < booléens < tableaux < objets) ;
• FOR UPDATE, FOR NO KEY UPDATE, FOR SHARE, FOR KEY SHARE, avec NOWAIT ou SKIP LOCKED.

Tableau 29.1. Types de conversion#

Les noms de MIRAJ (SIGNED, DATETIME, BINARY…) restent acceptés, et le préfixe pg_catalog. est ignoré.

29.3 Types de colonnes#

Une colonne serial qui n'est pas à elle seule la clé primaire reçoit une contrainte d'unicité (une colonne AUTO_INCREMENT de MIRAJ doit être une clé).

Une colonne jsonb se comporte comme une colonne json (même stockage, mêmes fonctions), mais garde son type : elle est annoncée jsonb (OID 3802) dans RowDescription, pg_attribute.atttypid, format_type, pg_typeof et information_schema.columns.data_type, au travers d'une vue ou d'une table dérivée, et col -> 'clé', col #> '{a,b}' sur elle rendent du jsonb ; une colonne json reste json (OID 114). ALTER COLUMN … TYPE jsonb (ou json) change le type annoncé. En langage MIRAJ, la colonne s'écrit JSONB (voir 5).

29.4 Définition des données#

• CREATE SCHEMA [IF NOT EXISTS] nom crée une base, DROP SCHEMA nom [CASCADE | RESTRICT] la supprime avec tout son contenu : chaque base MIRAJ est un schéma PostgreSQL.
• CREATE TEMP TABLE, CREATE TEMPORARY TABLE : table temporaire de la session ; CREATE UNLOGGED TABLE : table ordinaire.
• Contraintes de colonne : NOT NULL, NULL, DEFAULT expr, PRIMARY KEY, UNIQUE, CHECK (…) (qui peut citer d'autres colonnes), REFERENCES t (c), précédées ou non de CONSTRAINT nom ; DEFERRABLE, INITIALLY DEFERRED sans effet ; COLLATE "C" ignoré.
• DEFAULT 'texte'::character varying, DEFAULT now(), DEFAULT CURRENT_TIMESTAMP, DEFAULT 'a' || 'b' : la valeur est gardée en texte MIRAJ.
• GENERATED ALWAYS AS (expr) STORED : colonne générée.
• TRUNCATE [TABLE] [ONLY] t [RESTART IDENTITY | CONTINUE IDENTITY] [CASCADE | RESTRICT] : une table à la fois ; le compteur d'auto-incrément repart de 1.
• CREATE [OR REPLACE] VIEW, CREATE INDEX nom ON t (c) (et USING btree), DROP TABLE … CASCADE.

29.5 Session et transactions#

Les paramètres que PostgreSQL annonce aux clients (application_name, client_encoding, DateStyle, TimeZone, extra_float_digits…) sont tenus par le serveur réseau (voir 15.10.4).

29.6 Extensions MIRAJ disponibles#

Elles s'écrivent avec la lexicographie PostgreSQL (identifiants entre guillemets, chaînes standard) :

• index vectoriels (CREATE VECTOR INDEX, CREATE INDEX … USING hnsw), distances <->, <#>, <+> ; index plein texte (CREATE FULLTEXT INDEX), MATCH (…) AGAINST (…) ;
• vues en cache (CREATE CACHED VIEW, REFRESH VIEW) ;
• points d'accès REST (CREATE ENDPOINT, corps limité à un seul SELECT en langage PostgreSQL), événements de changement (LISTEN, UNLISTEN, NOTIFY, WAIT FOR CHANGES) ;
• BACKUP DATABASE, RESTORE DATABASE, transactions XA (XA START…), KILL, USE ;
• SHOW de MIRAJ (SHOW TABLES, SHOW DATABASES, SHOW CREATE TABLE, SHOW PROCESSLIST, SHOW VARIABLES, SHOW CLUSTER STATUS…), variables @@nom et @v ;
• partitionnement, tables temporelles (FOR SYSTEM_TIME), QUALIFY, GROUP BY … WITH ROLLUP.

29.7 Syntaxe MIRAJ refusée#

Ces formes ont un autre sens en PostgreSQL, ou n'y existent pas : elles reçoivent une erreur de syntaxe (42601) dont le message nomme la construction.

29.8 Pas encore pris en charge#

29.9 Catalogue pg_catalog#

Les clients PostgreSQL décrivent la base en lisant les tables du système (pg_class, pg_attribute…). Une instance en langage PostgreSQL les tient dans une base virtuelle pg_catalog, construite depuis le catalogue de MIRAJ à chaque instruction qui la lit, comme information_schema : rien n'y est écrit (une écriture reçoit 42501), et une instance en langage MIRAJ ne la voit pas (ni ses tables, ni les fonctions de ce chapitre).

Modèle : une seule base PostgreSQL, miraj (pg_database) ; chaque base de MIRAJ est un schéma (pg_namespace). La base courante est en tête du chemin de recherche : ses tables sont « visibles » (pg_table_is_visible), celles des autres bases se nomment base.table. Comme dans PostgreSQL, pg_catalog passe avant tout schéma : SELECT * FROM pg_class lit la table du catalogue (une expression WITH du même nom l'emporte).

OID : les types de base gardent ceux de PostgreSQL (int4 23, text 25, numeric 1700, tableaux _int4 1007…), les tables du catalogue aussi ('pg_class'::regclass vaut 1259). Les objets de MIRAJ reçoivent un OID synthétique et stable : un hachage de leur genre, de leur base et de leur nom, supérieur à 16 384, qui ne change pas d'un redémarrage à l'autre (renommer un objet change son OID). Le type vector est celui de l'extension pgvector (OID 16385, extension vector dans pg_extension).

Tableau 29.2. Tables de pg_catalog#

Les colonnes sont celles de PostgreSQL 16, dans leur ordre, avec leur type (oid, name, "char", int2, bool…) ; les tableaux du catalogue (conkey, proargnames, indkey…) sont écrits en texte, {1,3} ou 1 3 pour int2vector.

Fonctions de PostgreSQL#

Elles n'existent qu'en langage PostgreSQL, et passent avant une fonction de MIRAJ de même nom (version(), length()…).

Conversions : 'clients'::regclass donne l'OID de la table (42P01 si elle n'existe pas), c.oid::regclass son nom (qualifié par sa base hors de la base courante) ; de même ::regtype (format_type), ::regproc, ::regnamespace, ::regrole.

Formes reconnues pour les requêtes de catalogue : tableau[n] (i.indkey[0], (current_schemas(true))[1]), x = ANY (tableau) et x <> ALL (tableau) sur un tableau du catalogue, generate_series(début, fin [, pas]) dans un FROM (avec AS s(n)), alias de colonnes d'une table dérivée ((SELECT …) AS x(a, b)), alias après AS qui est un mot réservé (AS default). Une condition (a = b, x IS NULL, EXISTS (…)…) placée dans la liste d'un SELECT est un booléen (t / f).

Quelques requêtes figées des clients usent de formes que le moteur n'exécute pas (fonctions qui rendent des lignes comme information_schema._pg_expandarray, pg_partition_ancestors, tableaux construits par ARRAY(SELECT …) corrélé) : le serveur en reconnaît le texte (psql 10 à 18, pgjdbc 42.x, Npgsql 6 à 10) et les réécrit en requêtes équivalentes sur le même catalogue.

Définitions et commentaires#

COMMENT ON TABLE clients IS 'Les clients';
COMMENT ON COLUMN clients.nom IS 'Nom complet';    -- IS NULL retire le commentaire
CREATE TABLE lignes (id serial PRIMARY KEY, commande bigint REFERENCES commandes, qte int);
CREATE INDEX lignes_qte ON lignes (qte);
DROP INDEX lignes_qte;                              -- sans table, comme PostgreSQL
SHOW ALL;
SHOW statement_timeout;

• REFERENCES t sans colonnes désigne la clé primaire de t (42830 si elle n'en a pas).
• DROP INDEX [IF EXISTS] [base.]nom cherche la table qui porte l'index dans la base courante (ou celle écrite) ; un seul index par instruction.
• COMMENT ON porte sur une table ou une colonne ; un commentaire de colonne redéfinit la colonne (la table est réécrite).

29.10 Définitions gardées et changement de langage#

Une vue, une valeur par défaut, une contrainte CHECK, une colonne générée, un partitionnement, un point d'accès REST ou un filtre LISTEN écrits en langage PostgreSQL sont gardés en texte MIRAJ par le catalogue (la requête est réécrite depuis son arbre). Une instance peut donc changer de langage (arrêt, modification de sql_mode, redémarrage) : ses objets restent lisibles et utilisables dans l'autre langage.

Les expressions gardées avec une table (colonne générée, index sur expression, valeur par défaut, contrainte CHECK) gardent aussi le langage de leur création : elles se calculent toujours selon ses règles, quel que soit le langage de l'instance qui écrit ou lit la table. Une colonne GENERATED ALWAYS AS (split_part(code, '-', 1)) STORED ou un index sur (doc ->> 'k') créés en langage PostgreSQL donnent la même valeur dans une instance rouverte en langage MIRAJ (split_part, strpos, jsonb_typeof… y restent trouvées, length compte des caractères, ->> rend NULL pour null), et une table créée en langage MIRAJ garde les règles de MIRAJ. Le langage MIRAJ lui-même ne gagne pas les fonctions de PostgreSQL : une requête écrite en langage MIRAJ ne peut pas appeler split_part (1305).

La fonction de partitionnement (et de sous-partitionnement) et la requête d'une vue suivent la même règle : une table PARTITION BY LIST (length(a)) créée en langage PostgreSQL range ses lignes selon le nombre de caractères dans toute instance (ADD / REORGANIZE PARTITION compris), et une vue créée en langage PostgreSQL se lit dans ce langage — sa condition WITH CHECK OPTION aussi —, une vue créée en langage MIRAJ dans celui de MIRAJ. Seul un UPDATE ou un DELETE au travers d'une vue, réécrit en une instruction sur la table, compile la condition de la vue dans le langage de la session : une fonction propre à l'autre langage y est refusée (1305).

L'affichage suit le langage de l'instance : SHOW CREATE TABLE, SHOW CREATE VIEW et information_schema.VIEWS.VIEW_DEFINITION rendent, en langage PostgreSQL, une définition écrite en PostgreSQL (identifiants entre guillemets, ||, LIMIT ALL, types PostgreSQL, index en CREATE INDEX), quel que soit le langage dans lequel l'objet a été créé. Une colonne BOOLEAN (gardée en TINYINT(1)) s'y écrit boolean ; ENUM(…) et SET(…) restent écrits comme MIRAJ les lit (extension, valeurs en chaînes standard), pour que le texte recrée la même colonne. SHOW CREATE SEQUENCE, SHOW CREATE ENDPOINT, SHOW CREATE USER et SHOW GRANTS rendent leur texte avec la lexicographie de PostgreSQL (identifiants "…", chaînes standard) : ces lignes se rejouent telles quelles sur une instance PostgreSQL (miraj-dump --users). Une routine ou un déclencheur écrit en PL/pgSQL est gardé en texte MIRAJ et son texte d'origine à côté (29.13) ; écrit en langage MIRAJ, son corps reste affiché en langage MIRAJ, comme celui d'un événement.

CREATE VIEW resume AS
  SELECT nom || ' (' || etat || ')' AS libelle FROM articles WHERE nom ILIKE 'st%' FETCH FIRST 5 ROWS ONLY;
SHOW CREATE VIEW resume;
-- CREATE VIEW "resume" AS select ((("nom" || ' (') || "etat") || ')') as "libelle" from "articles"
--   where ("nom" like 'st%') limit 5

Une colonne calculée sans alias garde son nom écrit (SELECT a + 1 FROM t nomme la colonne a + 1).

29.11 Écritures : RETURNING, ON CONFLICT et COPY#

RETURNING#

INSERT, UPDATE et DELETE acceptent une clause RETURNING, écrite comme la liste d'un SELECT (*, t.*, expressions, alias) : l'instruction rend une ligne par ligne écrite, dans l'ordre des écritures, puis son étiquette habituelle (INSERT 0 2, UPDATE 1, DELETE 3).

INSERT INTO clients (nom) VALUES ('Dupont'), ('Durand') RETURNING id, nom, cree_le;
UPDATE stock SET quantite = quantite - 1 WHERE article = 12 RETURNING quantite;
DELETE FROM paniers WHERE expire < now() RETURNING *;
INSERT INTO t AS x (code, n) VALUES ('a', 1) ON CONFLICT (code) DO UPDATE SET n = x.n + 1 RETURNING x.n;

• INSERT et UPDATE rendent les valeurs après l'écriture : valeur d'auto-incrément (SERIAL, identité), DEFAULT, colonnes générées, et ce qu'un déclencheur BEFORE a changé ; DELETE rend les valeurs d'avant la suppression. Les lignes rendues sont lues dans la même transaction que l'écriture, verrous encore tenus.
• Les expressions sont vérifiées avant toute écriture (une colonne inconnue, 42703, ne modifie rien) ; une sous-requête de la clause voit l'état d'avant l'instruction, comme PostgreSQL.
• Protocole étendu : Describe de l'instruction décrit les colonnes de la clause (RowDescription), ce qu'attendent getGeneratedKeys de pgjdbc et ExecuteReader de Npgsql.
• Droits : SELECT sur la table, en plus du droit d'écriture.
• Un UPDATE … RETURNING n'est jamais différé (deferred_update) : sa ligne est écrite tout de suite pour être relue.
• Non pris en charge (0A000) : au travers d'une vue, sur une table partitionnée (édition Cluster), dans un UPDATE ou un DELETE multi-tables (DELETE … USING), sous-requête corrélée à la ligne écrite.

RETURNING n'existe qu'en langage PostgreSQL : le langage MIRAJ reste inchangé.

ON CONFLICT#

INSERT INTO t (id, code) VALUES (1, 'a') ON CONFLICT DO NOTHING;
INSERT INTO t (id, code) VALUES (1, 'a') ON CONFLICT (code) DO NOTHING;
INSERT INTO t (id, code) VALUES (1, 'a') ON CONFLICT ON CONSTRAINT t_pkey DO NOTHING;
INSERT INTO t AS x (code, n) VALUES ('a', 5), ('b', 2)
  ON CONFLICT (code) DO UPDATE SET n = x.n + EXCLUDED.n WHERE x.n < 100;

• La cible désigne la contrainte d'unicité arbitre : liste de colonnes (la contrainte dont les colonnes sont exactement celles-ci, sinon 42P10) ou ON CONSTRAINT nom (clé primaire nommée <table>_pkey comme dans pg_catalog, ou nom d'une contrainte UNIQUE ; inconnue : 42704). Sans cible (DO NOTHING seulement), toute contrainte d'unicité est arbitre.
• Seul l'arbitre est traité : une ligne qui viole une autre contrainte d'unicité reçoit l'erreur 23505, comme dans PostgreSQL (alors que ON DUPLICATE KEY UPDATE réagit à toute clé).
• DO NOTHING écarte la ligne proposée sans avertissement ; les autres erreurs (NOT NULL, CHECK, clés étrangères) restent des erreurs.
• DO UPDATE SET col = expr, … modifie la ligne existante : une colonne seule ou qualifiée par la table (ou son alias, INSERT INTO t AS x) désigne la ligne existante, EXCLUDED.col la ligne proposée (valeurs par défaut et déclencheur BEFORE INSERT compris), DEFAULT la valeur par défaut. WHERE condition (sur la ligne existante et EXCLUDED) laisse la ligne telle quelle quand elle n'est pas vraie. Les déclencheurs UPDATE de la table s'exécutent.
• Une ligne insérée ou modifiée par l'instruction ne peut pas être modifiée une seconde fois par elle (deux lignes proposées de même clé) : 21000, rien n'est écrit.
• Compte : une ligne par ligne insérée ou modifiée (INSERT 0 2), aucune pour DO NOTHING ou un WHERE faux ; une ligne en conflit modifiée sans changement de valeur compte aussi.
• Non pris en charge (0A000) : cible faite d'expressions (ON CONFLICT (lower(code))), d'index partiel (WHERE), avec COLLATE ou classe d'opérateurs ; DO UPDATE SET (a, b) = (…) ; au travers d'une vue ou sur une table partitionnée.

COPY#

Options : FORMAT text (défaut) ou FORMAT csv, DELIMITER 'c', NULL 'texte', HEADER (sautée à la lecture, écrite en tête à l'écriture), QUOTE 'c', ESCAPE 'c' (CSV), ENCODING 'UTF8', FREEZE (sans effet). La forme ancienne des options est acceptée (WITH DELIMITER AS '|' NULL AS '', WITH CSV HEADER), telle que l'envoient psycopg2 (copy_from, copy_to) et les vieux scripts.

• Format texte : champs séparés par une tabulation, NULL écrit \N, échappements \t, \n, \r, \\, \b, \f, \v, octal (\101) et hexadécimal (\x41). CSV : virgule, guillemets doubles, "" pour un guillemet dans un champ cité, NULL = champ vide non cité, "" = chaîne vide ; un champ cité peut contenir des fins de ligne.
• Valeurs reçues : texte en UTF-8 (22021 sinon) ; booléens écrits comme PostgreSQL (t, true, yes, on, 1…, sinon 22P02) ; bytea en hexadécimal \x… ou forme échappée ; date-heure avec décalage (2026-10-06 10:00:00+02) chargée sans le décalage, comme un littéral ; les autres conversions sont celles de LOAD DATA dans le sql_mode de la session (mode strict : une valeur invalide est une erreur). Valeurs écrites : celles qu'un SELECT rend en texte (t/f, bytea en \x…, dates ISO).
• Une ligne qui n'a pas autant de champs que de colonnes : 22P04 (missing data for column "nom", extra data after last expected column), avec la ligne en contexte (COPY t, line 3).
• COPY … FROM STDIN s'exécute comme un INSERT (droit INSERT, vérifié avant que le client n'envoie les données ; déclencheurs, contraintes, clés étrangères) et en une seule instruction : une erreur, un abandon du client (CopyFail, 57014) ou une annulation (Ctrl-C, 57014) n'écrivent rien. Après une erreur au milieu des données, le serveur lit et ignore la suite jusqu'à la fin de l'envoi, puis rend l'erreur. Le serveur lit d'abord les colonnes de la table : le droit SELECT est aussi exigé.
• COPY … TO STDOUT exige le droit SELECT, comme la requête correspondante.
• Sans liste de colonnes, les colonnes générées sont exclues, dans les deux sens.
• psql : \copy t from 'fichier.csv' with (format csv, header) et \copy t to 'sortie.txt' lisent et écrivent le fichier sur le poste du client, par COPY … FROM STDIN / TO STDOUT.
• Non pris en charge (0A000) : FORMAT binary, HEADER MATCH, FORCE_QUOTE, FORCE_NOT_NULL, FORCE_NULL, DEFAULT, ON_ERROR, COPY … FROM … WHERE, un autre encodage qu'UTF-8, et COPY dans le protocole étendu (instruction préparée). Fichiers du serveur : COPY t FROM '/chemin', COPY t TO '/chemin' et PROGRAM sont refusés (0A000) ; pour charger ou écrire un fichier du serveur, LOAD DATA INFILE et SELECT … INTO OUTFILE restent disponibles en langage MIRAJ, soumis à secure_file_priv et au droit FILE.

Les données d'un COPY … FROM STDIN sont gardées en mémoire jusqu'à la fin de l'envoi, puis chargées par le chemin de LOAD DATA LOCAL INFILE (sans fichier temporaire).

29.12 Outils MIRAJ sur une instance PostgreSQL#

Les outils de MIRAJ se connectent aux deux langages par le même client (miraj-connect) : ils reconnaissent le langage de l'instance à la connexion et lui envoient leurs instructions dans ce langage.

Choix du protocole : option --protocol auto | miraj | postgresql de chaque outil (clé protocol de la table [monitor] de proxy.toml), auto par défaut.

• auto envoie d'abord une demande de chiffrement PostgreSQL (SSLRequest, ou GSSENCRequest avec --no-ssl) et lit un octet. S ou N : instance PostgreSQL, l'échange continue sur la même connexion (TLS, puis SCRAM-SHA-256 ou mot de passe en clair). Tout autre octet est le premier de la salutation du protocole hérité : la connexion est refermée et rouverte en langage MIRAJ. Le serveur MIRAJ reconnaît cette sonde et la ferme sans la compter dans Aborted_connects (une ligne « client PostgreSQL sur une instance en langage MIRAJ » dans son journal).
• Le langage reconnu est retenu par point d'accès (hôte:port) pour la durée du processus : une seule connexion de plus, une fois, vers une instance MIRAJ.
• miraj et postgresql imposent le protocole (un client hérité sur une instance PostgreSQL attend le délai de connexion du serveur avant d'être fermé).
• Les options TLS sont les mêmes dans les deux protocoles (--ssl, --ssl-ca, --ssl-insecure, --no-ssl) ; le proxy relaie la sonde comme les autres octets.

Export d'une instance PostgreSQL (miraj-dump) :

• tables par SHOW CREATE TABLE (déjà en PostgreSQL), lignes en INSERT multi-lignes : identifiants "…", chaînes standard, booléens true/false, bytea en '\x…'::bytea, flottants au plus court (-1.5e300) ; séquences, vues, points d'accès REST, comptes et droits dans la même lexicographie, sans DELIMITER ;
• en tête : SET client_encoding = 'UTF8' et SET foreign_key_checks = 0 ; la lecture est faite dans une transaction REPEATABLE READ, READ ONLY ;
• les procédures, fonctions et déclencheurs écrits en PL/pgSQL sont écrits dans ce langage (29.13) ; ceux écrits en langage MIRAJ (créés avant un changement de langage) sont traduits au mieux en PL/pgSQL, sinon cités en commentaire avec la raison, comme les événements ;
• --users : le mot de passe est repris par son empreinte (IDENTIFIED BY PASSWORD '*…'). Un compte rechargé n'a pas de vérificateur SCRAM-SHA-256 : il se connecte en mot de passe en clair (sous TLS ou en boucle locale) jusqu'à ce que son mot de passe soit redéfini (ALTER USER … IDENTIFIED BY …), comme un compte créé avant SCRAM (15.10.2). L'outil le rappelle ;
• un export d'une instance MIRAJ ne se recharge pas tel quel dans une instance PostgreSQL, ni l'inverse : l'export suit le langage de l'instance lue.

miraj-dump --db shop --users -r shop.sql --port 7007
psql -h 127.0.0.1 -p 7008 -U root -d miraj -v ON_ERROR_STOP=1 -f shop.sql

(sous Windows, PGCLIENTENCODING=UTF8 ou chcp 65001 pour les textes accentués.)

Cluster : tous les nœuds d'un cluster ont le même langage. Un nœud d'un autre langage est refusé à l'adhésion (erreur 9054, consignée par les deux nœuds) : voir le 16.12.

29.13 Routines et déclencheurs (PL/pgSQL)#

Une instance en langage PostgreSQL accepte CREATE FUNCTION et CREATE PROCEDURE écrits en LANGUAGE plpgsql ou LANGUAGE sql, et les déclencheurs CREATE TRIGGER … EXECUTE FUNCTION f(). Ils sont traduits à la création en routines et déclencheurs de MIRAJ, exécutés par le même interpréteur que ceux écrits en langage MIRAJ. Ce qui sort du sous-ensemble décrit ici est refusé à la création (0A000, la construction est nommée dans le message) : rien n'est gardé de travers.

CREATE FUNCTION add_tax(price numeric(10,2), rate numeric(4,2) DEFAULT 0.2)
RETURNS numeric(10,2) LANGUAGE plpgsql IMMUTABLE AS $$
DECLARE
  result numeric(10,2);
BEGIN
  result := price * (1 + rate);
  RAISE NOTICE 'tax of % is %', price, result - price;
  RETURN round(result, 2);
END;
$$;

SELECT add_tax(10);              -- 12.00, avec l'avis « NOTICE: tax of 10.00 is 2.00 »

CREATE PROCEDURE bump(INOUT n int, step int DEFAULT 1) LANGUAGE plpgsql AS $$
BEGIN
  n := n + step;
END $$;

CALL bump(41);                   -- une ligne : n = 42

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

CREATE TRIGGER set_updated_at BEFORE UPDATE ON products
  FOR EACH ROW EXECUTE FUNCTION set_updated_at();

En-tête de CREATE FUNCTION et CREATE PROCEDURE#

Une fonction LANGUAGE sql rend le résultat de son dernier SELECT (première ligne, NULL sans ligne) ; ses instructions précédentes sont des écritures. Une procédure LANGUAGE sql n'exécute que des écritures.

Sous-ensemble PL/pgSQL#

Déclencheurs#

CREATE [OR REPLACE] TRIGGER nom {BEFORE | AFTER} {INSERT | UPDATE [OF col, …] | DELETE} [OR …]
  ON table FOR EACH ROW [WHEN (condition)] EXECUTE {FUNCTION | PROCEDURE} f([arguments]);
DROP TRIGGER [IF EXISTS] nom ON table [CASCADE | RESTRICT];

• La fonction f est une fonction RETURNS trigger écrite en PL/pgSQL, sans paramètre. Elle lit NEW, OLD (NULL quand la ligne n'existe pas : OLD d'un INSERT, NEW d'un DELETE), TG_OP, TG_NAME, TG_WHEN, TG_LEVEL, TG_TABLE_NAME, TG_RELNAME, TG_TABLE_SCHEMA, TG_NARGS, TG_ARGV[i] ; elle modifie NEW dans un déclencheur BEFORE.
• RETURN NEW (ou OLD pour un DELETE) laisse la ligne s'écrire ; RETURN NULL dans un déclencheur BEFORE écarte la ligne : elle n'est ni insérée, ni modifiée, ni supprimée, n'est pas comptée, et les déclencheurs suivants ne s'exécutent pas. RETURN OLD d'un BEFORE UPDATE garde l'ancienne ligne. La valeur rendue par un déclencheur AFTER est ignorée. Atteindre la fin de la fonction sans RETURN : 2F005.
• Une même fonction peut servir à plusieurs déclencheurs, sur plusieurs tables ; deux tables peuvent avoir un déclencheur de même nom. Remplacer la fonction (CREATE OR REPLACE FUNCTION) met à jour les déclencheurs qui l'appellent.
• Les déclencheurs d'une table, d'un moment et d'un événement s'exécutent dans l'ordre alphabétique de leurs noms, comme dans PostgreSQL.
• Appeler directement une fonction de déclencheur (SELECT f()) : 0A000.

Un déclencheur PostgreSQL devient un déclencheur MIRAJ par événement, nommé nom$table$événement (set_updated_at$products$update), dont le corps est la traduction de la fonction pour cet événement. Une instance en langage MIRAJ les voit sous ces noms (SHOW TRIGGERS) ; en langage PostgreSQL, pg_trigger, \d table et DROP TRIGGER nom ON table les voient sous leur nom PostgreSQL, une ligne par déclencheur.

Refusé (0A000)#

RETURNS SETOF, RETURNS TABLE, RETURN NEXT, RETURN QUERY (fonctions qui rendent des lignes) ; fonction à plusieurs paramètres OUT, paramètre ou résultat de type composite (RECORD, table) ; EXECUTE (SQL dynamique), FOR … IN EXECUTE ; FOREACH et tableaux ; curseurs à arguments, OPEN … FOR, refcursor, FETCH autre que NEXT, MOVE ; COMMIT / ROLLBACK dans une procédure ; VARIADIC, types polymorphes (anyelement…) ; INSERT … ON CONFLICT, UPDATE | DELETE … RETURNING, RETURNING d'une autre ligne qu'un INSERT … VALUES dans le corps ; un SELECT sans INTO dans le corps (42601, comme PostgreSQL) ; autres instructions (DDL) dans le corps ; LANGUAGE c, internal, plpython… ; clauses SET, WINDOW, TRANSFORM, SUPPORT ; BEGIN ATOMIC ; déclencheurs FOR EACH STATEMENT (ou sans FOR EACH ROW), INSTEAD OF, TRUNCATE, CONSTRAINT TRIGGER, REFERENCING ; ALTER FUNCTION | PROCEDURE | TRIGGER ; blocs anonymes DO $$ … $$.

Événements planifiés (CREATE | ALTER EVENT) : refusés en langage PostgreSQL. PostgreSQL n'en a pas (pg_cron est une extension) et un événement de MIRAJ écrit en PL/pgSQL serait une syntaxe inventée. Les événements créés en langage MIRAJ, avant un changement de langage, continuent de s'exécuter.

Stockage, affichage et changement de langage#

• Le catalogue garde le texte MIRAJ de la traduction (comme une vue, 29.10) : c'est lui qui s'exécute, et une instance redémarrée en langage MIRAJ exécute et affiche (SHOW CREATE FUNCTION, en langage MIRAJ) les routines et déclencheurs créés en PL/pgSQL.
• Le texte d'origine (CREATE FUNCTION … AS $$…$$, CREATE TRIGGER …) est gardé à côté : l'affichage en langage PostgreSQL en vient, au caractère près pour le corps (prosrc, pg_get_functiondef, \sf), et une fonction de déclencheur remplacée est retraduite depuis lui. De retour en langage PostgreSQL, les définitions affichées sont les mêmes qu'avant le changement.
• Une routine ou un déclencheur écrit en langage MIRAJ reste affiché en langage MIRAJ (SHOW CREATE, pg_get_functiondef).

SHOW CREATE FUNCTION | PROCEDURE rend, pour une routine écrite en PostgreSQL, le texte de pg_get_functiondef ; SHOW CREATE TRIGGER nom (nom PostgreSQL ou nom MIRAJ) celui de pg_get_triggerdef. Comme MIRAJ applique la précision d'un paramètre ou du résultat (une valeur rendue est convertie au type déclaré), l'en-tête affiché la garde (numeric(10,2) ; PostgreSQL l'oublie et écrit numeric).

Catalogue, psql et outils#

• pg_proc : prosrc (corps tel qu'écrit), prolang (sql 14, plpgsql 13600, voir pg_language), prokind, provolatile, proisstrict, prosecdef, pronargs, pronargdefaults, prorettype (void 2278, trigger 2279), proargtypes, proallargtypes, proargmodes, proargnames ; pg_get_function_result, pg_get_function_arguments, pg_get_function_identity_arguments, pg_get_functiondef, 'nom'::regproc, 'nom(types)'::regprocedure.
• pg_trigger : une ligne par déclencheur PostgreSQL (tgtype de ses événements, tgfoid, tgargs, tgattr, tgqual) ; pg_get_triggerdef(oid [, pretty]).
• psql 18 : \df, \df+ (volatilité, sécurité, langage), \sf, \sf+, \d table (section « Triggers »).
• miraj-dump écrit les routines et déclencheurs en PL/pgSQL (CREATE OR REPLACE FUNCTION … AS $function$…$function$, DROP … CASCADE d'abord ; un déclencheur par nom et par table, après les lignes), sans DELIMITER : le script se recharge par psql -f et miraj-dump restore. Une routine écrite en langage MIRAJ y est traduite au mieux en PL/pgSQL, sinon citée en commentaire.
• miraj-migrate vers une instance en langage PostgreSQL traduit les routines et déclencheurs de la source en PL/pgSQL (17) ; ce qui ne se traduit pas est cité avec un avertissement.

Écarts#

• Un gestionnaire EXCEPTION n'annule pas les écritures faites par le bloc avant l'erreur (PostgreSQL les annule par un point de sauvegarde implicite) ; l'instruction en faute, elle, est annulée. Le gestionnaire choisi est le plus précis qui couvre l'erreur (numéro, SQLSTATE, puis OTHERS), et non le premier écrit.
• Une fonction ne peut pas s'appeler elle-même, directement ou non (1424 : récursion refusée, comme pour les routines de MIRAJ).
• UPDATE OF col déclenche quand la valeur d'une de ces colonnes change (casse comprise), et non quand la colonne est citée par SET sans changer.
• Les types %TYPE / %ROWTYPE et les champs d'un RECORD sont résolus à la création : les tables (et les requêtes d'un FOR r IN requête) doivent exister ; une table modifiée ensuite n'est pas relue (recréer la routine). De même, TG_TABLE_NAME est fixé à la création du déclencheur.
• numeric sans précision vaut numeric(38,10) (29.3) : un résultat s'affiche avec dix décimales.
• Les comparaisons de textes ignorent la casse (collations de MIRAJ), dans le corps comme ailleurs.
• Les fonctions propres à PostgreSQL (pg_sleep, format…) citées dans un corps ne s'exécutent que sur une instance en langage PostgreSQL ; une instance repassée en langage MIRAJ les refuse à l'exécution.
• Un avis RAISE ne remonte pas les champs DETAIL / HINT.
• Les variables d'une routine masquent une colonne de même nom dans ses requêtes (PostgreSQL signale l'ambiguïté).
• Pas de comparaison avec un serveur PostgreSQL de référence pour ce corpus (le serveur local n'était pas joignable sans modifier sa configuration) : les comportements décrits suivent la documentation de PostgreSQL 16.

29.14 Pilote ODBC et bibliothèque Python#

Le pilote MIRAJ ODBC Driver (chapitre 28) et la bibliothèque Python miraj (26.12) se connectent aussi à une instance PostgreSQL, sans changement d'API, par le même client PostgreSQL que les outils (29.12) :

• Choix du protocole : mot-clé PROTOCOL de la chaîne de connexion ou de la source ODBC, paramètre protocol de miraj.connect() ; auto (défaut), miraj ou postgresql, avec la même détection que les outils.
• Paramètres : les marqueurs de l'application (? d'ODBC, %s / %(nom)s de Python) deviennent $1, $2… d'une instruction préparée nommée du serveur, réutilisée (SQLPrepare puis SQLExecute répétés, executemany) ; valeurs envoyées en texte avec leur type.
• Résultats : colonnes décrites avec leur type PostgreSQL, leur précision et leur échelle, leur table et leur colonne d'origine, leur nullabilité et l'auto-incrément (lus dans pg_attribute) ; SQL_ATTR_MAX_ROWS limite les lignes envoyées par le serveur.
• ODBC : SQLGetInfo annonce PostgreSQL et la citation " ; les fonctions de catalogue rendent TABLE_CAT = miraj et la base MIRAJ en TABLE_SCHEM ; SQLCancel envoie une demande d'annulation (CancelRequest), l'instruction échoue en HY008.
• Python : mogrify écrit les littéraux de ce langage ; copy_from, copy_to et copy_expert passent par COPY … FROM STDIN / TO STDOUT ; bool et json décodés, vecteur en texte [x,y,…] ; lastrowid vaut None (INSERT … RETURNING).
• Erreurs : SQLSTATE de PostgreSQL, sans code MIRAJ.

Les pilotes PostgreSQL tiers (psqlODBC, psycopg2/psycopg) fonctionnent aussi.