30. Langage PostgreSQL

Une instance démarrée avec l'élément POSTGRESQL de sql_mode (voir 16.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.

Ce chapitre est une liste condensée. Le manuel du langage PostgreSQL décrit le même langage en détail, chapitre par chapitre (prise en main, types, DDL, DML, requêtes, fonctions, transactions, comptes, PL/pgSQL, catalogue et outils), avec des exemples complets.

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).

30.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.

Un nom de colonne (ou d'index, ou un alias) admet les lettres et chiffres non ASCII. Écrit sans guillemets, un nom qui porte une lettre non ASCII est replié en minuscules, comme PostgreSQL en UTF-8 (PRÉNOM nomme prénom) ; entre guillemets il est gardé, et la casse de ses lettres non ASCII compte ("Âge" n'est pas âge). Les noms de tables, de vues, de séquences, de schémas et de routines restent ASCII (42000), ainsi que tout nom portant un signe combinant ou une ponctuation.

30.2 Expressions et requêtes#

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

• / entre deux entiers est une division entière tronquée vers zéro (7 / 2 vaut 3, -7 / 2 vaut -3) ; avec un numeric, le quotient a l'échelle de PostgreSQL (7 / 2.0 vaut 3.5000000000000000) ;
• une division ou un modulo par zéro (1 / 0, 5 % 0, x / 0.0, mod(x, 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 ; le texte d'un entier peut s'écrire '0x1F', '0o17', '0b101', '1_000' ('0x1F'::int vaut 31), comme le nombre écrit sans guillemets : 0x1F, 0o17, 0b101 et 1_000 y sont des entiers (31, 15, 5 et 1000, et non une suite d'octets comme en langage MIRAJ), le souligné sépare aussi les chiffres d'un décimal ou d'un exposant (1_000.5, 1e1_0), une forme invalide (1__0, 1_, 0x) est une erreur de syntaxe (42601) ; '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 ;
• trim, btrim, ltrim, rtrim : pour le texte et pour bytea seulement (trim(42) : 42883, function btrim(integer) does not exist) ; sur un bytea (btrim(octets, octets), trim(LEADING octets FROM octets)), des octets sont retirés et le résultat est un bytea ;
• mauvais nombre d'arguments : 42883, au message de PostgreSQL (function btrim(unknown, unknown, unknown) does not exist).

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) ; une colonne jsonb range le document normalisé (voir 30.3) ; une colonne bpchar sans longueur range le texte sans ses espaces finaux. Une valeur par défaut écrite en chaîne est lue de même dans le type de la colonne : boolean DEFAULT 'yes', jsonb DEFAULT '{"b": 1, "a": 2}' (normalisée, relue '…'::jsonb).

Appels et priorités :

• :: lie plus fort que le moins unaire : -3.7::int vaut -(3.7::int), soit -4 (de même en langage MIRAJ, où :: est une extension) ;
• arguments nommés d'une fonction stockée, f(a => 1, b => 2) ou f(a := 1), après d'éventuels arguments positionnels ; un paramètre omis prend sa valeur par défaut (nom inconnu ou donné deux fois : 42883), de même dans CALL p(a => 1) ; refusés pour une fonction du serveur (0A000) ;
• nom de fonction entre guillemets, avec ou sans schéma : "f"(1) ;
• lastval() : dernière valeur servie à la session par nextval ou par une colonne serial / IDENTITY (55000 si aucune) ; currval('s') avant tout nextval('s') de la session : 55000 aussi (NULL en langage MIRAJ) ;
• fonction dans un FROM, SELECT * FROM f(…) [[AS] alias [(colonne)]] (ce qu'envoie cursor.callproc de psycopg2) : une ligne d'une colonne, nommée par l'alias, sinon par la fonction ; elle voit les tables qui la précèdent (LATERAL admis devant elle, en première position aussi). Plusieurs lignes : generate_series, unnest (ARRAY[a, b, …], tableau en texte '{1,2}'::int[], paramètre lié), regexp_matches, regexp_split_to_table, json[b]_each, json[b]_each_text, json[b]_array_elements[_text] et json[b]_object_keys (ces fonctions JSON pouvant citer les tables qui les précèdent ; les bornes de generate_series, non : 42703), avec WITH ORDINALITY [AS alias (colonne, rang)], et les fonctions stockées ensemblistes (RETURNS SETOF, RETURNS TABLE, voir 30.13 « Fonctions qui rendent des lignes ») à arguments constants (liste de définitions AS t (a int, …), WITH ORDINALITY après une fonction stockée : 0A000) ;
• fonctions ensemblistes dans la liste du SELECT (SELECT generate_series(1, 3), unnest(ARRAY[…]), regexp_matches, regexp_split_to_table, json[b]_array_elements[_text], json[b]_object_keys) : une ligne par valeur, comme PostgreSQL 10 et suivants ; plusieurs fonctions d'une même liste avancent ensemble, la plus longue fixant le nombre de lignes ; pas dans une liste regroupée, agrégée ou fenêtrée (0A000) ;
• alias de colonnes AS x (a, b) après toute source du FROM (table, vue, expression WITH, table dérivée, VALUES, fonction) : les premières colonnes sont renommées, les suivantes gardent leur nom ; trop d'alias : 42P10 ;
• tableaux d'une expression : ARRAY[…], '{…}'::type[], tableau[n], array_agg, rendus sous la forme texte de PostgreSQL ({1,2,NULL}) et annoncés sous le type tableau de leurs éléments (int4[], text[]…) ; une colonne ne peut pas être de type tableau (30.8) ;
• ONLY table, ONLY (table) et table * dans FROM, UPDATE, DELETE, TRUNCATE, LOCK et ALTER TABLE (la forme de pg_dump) : sans effet, MIRAJ n'ayant pas d'héritage de tables ;
• GROUP BY sur la clé primaire entière d'une table : ses autres colonnes sont admises hors agrégat (dépendance fonctionnelle), t.* compris ; pas pour une vue, une table dérivée, une contrainte UNIQUE ni une clé citée en partie (42803).

Clauses de SELECT :

• SELECT DISTINCT ON (expr, …) : première ligne de chaque groupe, dans l'ordre de l'ORDER BY, qui doit commencer par ces expressions (42P10 sinon) ;
• LIMIT n, LIMIT ALL, OFFSET n [ROWS] (seul ou avant LIMIT), FETCH {FIRST | NEXT} [n] {ROW | ROWS} {ONLY | WITH TIES} (WITH TIES sans ORDER BY au même niveau : 42601) ;
• 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é.

30.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é). Comme une séquence de PostgreSQL, son compteur n'avance pas pour une valeur écrite, et un numéro tiré par une insertion qui échoue (23505) ou qu'annule un ROLLBACK est consommé.

Collation « C ». Les textes sont comparés et triés octet par octet, comme PostgreSQL en C.UTF-8 (SHOW lc_collate : C) : une colonne créée sans COLLATE dans une base sans interclassement propre est en utf8mb4_bin, de même que la connexion (collation_connection), les conversions ::text et les textes des routines PL/pgSQL ou SQL. 'a' et 'A' sont donc deux valeurs distinctes d'une clé primaire, d'une contrainte UNIQUE, d'un GROUP BY ou d'un DISTINCT, et ORDER BY place les majuscules d'abord ; seuls les espaces de fin sont ignorés par =. ILIKE et ~* ignorent la casse. Une collation de MIRAJ écrite (COLLATE utf8mb4_general_ci, sur une colonne ou dans une expression) reste appliquée. Les tables, routines et déclencheurs créés avant l'adoption de cette collation, et les colonnes des catalogues (sauf pg_settings), restent en utf8mb4_general_ci, insensibles à la casse.

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 6).

Un jsonb est normalisé comme dans PostgreSQL, à l'écriture dans une colonne jsonb (INSERT, UPDATE, COPY), par ::jsonb et dans le résultat des fonctions qui rendent un jsonb : dans chaque objet, une clé répétée ne garde que sa dernière valeur et les clés sont triées par longueur puis par ordre binaire ; l'espacement est canonique ({"a": 1, "b": [1, 2]}) et un nombre écrit avec un exposant est développé (1e2 : 100). '{"a":1,"b":2}'::jsonb = '{"b":2,"a":1}'::jsonb est donc vrai, et un objet ou un tableau écrit en texte face à un jsonb (doc = '{"b":2,"a":1}', doc || '{"c": 3}') est lu en jsonb. Un json garde le texte écrit. Deux jsonb se comparent par valeur, dans l'ordre propre au type (null, chaînes, nombres, booléens, tableaux, objets ; nombres par leur valeur) : '1.0'::jsonb = '1'::jsonb est vrai, '2'::jsonb < '10'::jsonb aussi, et ORDER BY, DISTINCT, GROUP BY, UNION, IN, BETWEEN et une clé de jointure suivent cette comparaison, ainsi qu'une clé primaire et une contrainte UNIQUE (1.0 et 1 y sont un doublon). ||, -, #- et jsonb_set n'existent que pour un jsonb : sur un json, ils sont refusés (42883). Un document rangé avant cette normalisation, ou écrit en langage MIRAJ (où la colonne se comporte comme un JSON et se compare par son texte), garde son texte jusqu'à sa prochaine écriture en langage PostgreSQL ; UPDATE t SET doc = doc::jsonb le normalise.

Une colonne ENUM est annoncée au client sous l'OID de son type énuméré <table>_<colonne>_enum, celui de pg_attribute ; ce type suit la valeur au travers d'une vue, d'une table dérivée, de CASE, de coalesce et de max / min (RowDescription, pg_attribute d'une vue, pg_typeof).

Une colonne de table reprise telle quelle est annoncée dans RowDescription (requête simple et Describe) sous son type déclaré, avec son modificateur, l'OID de sa table et son numéro de colonne : en lecture directe, après DISTINCT, après GROUP BY (clé de groupement, colonne que la clé primaire groupée détermine, *), au travers d'une table dérivée ou d'une expression de table ; au travers d'une vue, la relation annoncée est la vue. UNION, INTERSECT et EXCEPT gardent le type et le modificateur que partagent tous leurs membres, sans relation ; des varchar(n) de longueurs différentes, ou un varchar(n) uni à un texte écrit sans type, sont annoncés varchar sans modificateur, des char(n) de longueurs différentes bpchar sans modificateur. pg_typeof nomme le type déclaré d'une colonne après GROUP BY comme avant. Le langage MIRAJ n'annonce toujours aucune table d'origine après une agrégation ou une union.

Une colonne de vue est annoncée comme la requête qui définit la vue l'annonce quand on l'exécute directement (type, modificateur, pg_typeof ; la relation annoncée est la vue), que le résultat gardé de la vue soit servi ou sa requête rejouée, quel que soit le fuseau de la session ; pg_attribute et information_schema.columns décrivent la vue de même. Une colonne calculée suit le type de son expression (v || 'z' : text), une conversion écrite garde son type (::int2, ::real, ::varchar(5), ::char(3), ::timestamptz(0)…, que pg_get_viewdef rend sous son nom), une vue lue par une autre vue garde le type déclaré de la colonne de base. Le modificateur d'une conversion écrite (::numeric(10,2), ::varchar(5)) n'est pas annoncé (PostgreSQL l'annonce), dans une requête comme au travers d'une vue ; celui d'un ::char(n) l'est.

Une valeur annoncée character(n) (colonne char(n) lue directement, après GROUP BY ou DISTINCT, au travers d'une vue, d'une table dérivée ou d'une CTE ; conversion ::char(n)) est envoyée au client complétée par des espaces jusqu'à n caractères, en texte, en binaire et par COPY … TO STDOUT, comme PostgreSQL l'affiche. Le moteur la range et la calcule sans ses espaces finaux : length, ||, ::text et les comparaisons les ignorent ('ab '::char(5) || '|' vaut ab|), octet_length et LIKE / ILIKE les lisent (octet_length('ab'::char(5)) vaut 5, 'ab'::char(5) LIKE 'ab' est faux, LIKE 'ab ' et LIKE 'ab%' sont vrais). L'interface embarquée rend la valeur sans complément ; max, coalesce et CASE d'un char(n) sont annoncés text, sans complément ; concat, format, to_json, array_agg et ~ lisent la valeur sans ses espaces de remplissage. Un bpchar sans longueur n'est jamais complété. Le langage MIRAJ lit toujours un CHAR(n) sans espaces finaux.

Une colonne bit(n) est annoncée bit (OID 1560, modificateur n) et ses valeurs s'écrivent en chiffres binaires sur n positions, poids fort d'abord (101, 00000101 pour un bit(8)), dans un résultat comme dans COPY … TO. Elle reçoit une chaîne de n chiffres binaires ('101', B'101', X'1F', ou hexadécimale 'x1F' ; un autre nombre de positions est refusé, SQLSTATE 22026), en valeur écrite, en paramètre ou par COPY … FROM, ou un entier qui tient sur n positions (22026 sinon). Une colonne bit varying(n) garde au plus n chiffres (22001 au-delà) et est annoncée bit varying (OID 1562).

Dans une expression, une chaîne de bits est le texte de ses chiffres, annoncé bit ou bit varying : littéraux B'101' et X'1F', conversions ::bit(n), ::bit varying(n), ::integer et ::text, opérateurs &, |, #, ~, <<, >> (même longueur exigée pour les trois premiers, 22026) et ||, fonctions length, bit_length, octet_length, bit_count, get_bit, set_bit, substring, position, overlay. max, min, coalesce, nullif, CASE, greatest, une union, une vue, une table dérivée et pg_typeof gardent le type ; une colonne bit se compare à une chaîne de bits ou à un texte lu de même (b = '101'), position par position. Une colonne bit(n) lue seule, triée, regroupée ou comparée à une chaîne de n positions reste l'entier que range le moteur (les index servent) ; un calcul la lit sur ses n positions. Le texte canonique d'une vue ou d'une colonne générée garde ces formes en appels internes (pg_bit('101'), pg_bit_cast(x, n)), relus par les deux langages et réaffichés B'101', cast(x as bit(n)) en langage PostgreSQL.

Une chaîne de bits n'entre que dans une colonne bit(n) ou bit varying : toute autre colonne (bytea, text, entier, numeric, boolean, date, jsonb, uuid…) la refuse (42804, column "b" is of type bytea but expression is of type bit) par INSERT … VALUES, INSERT … SELECT, UPDATE, ON CONFLICT DO UPDATE et en valeur par défaut ; il faut la convertir (b::text, b::integer). En particulier X'DEADBEEF' n'est pas une suite d'octets en langage PostgreSQL : un bytea s'écrit '\xDEADBEEF' ou decode('DEADBEEF', 'hex').

La valeur par défaut d'une colonne bytea (DEFAULT '\x0aff'::bytea, DEFAULT '\x0aff') est gardée en suite d'octets ((X'0AFF') dans le texte de MIRAJ, '\x0aff'::bytea à l'affichage en langage PostgreSQL) ; une table créée avant cette règle garde son défaut tel qu'il a été rangé, jusqu'à ALTER TABLE … ALTER COLUMN … SET DEFAULT.

Écarts : un bit(n) a 64 positions au plus ; un entier reste admis dans une colonne bit(n) et s'y compare (b = 5) ; une chaîne de bits calculée gardée par CREATE TABLE … AS SELECT ou par une vue fait une colonne bit varying ; bit_and, bit_or, bit_xor et to_json d'une colonne bit(n) rendent un nombre ; une colonne bit varying ne reçoit qu'une chaîne de bits (un nombre, ou une colonne bit(n) lue sans conversion par INSERT … SELECT, est refusé : 42804). En langage MIRAJ, rien ne change : BIT(n) y est un entier, X'0F' une suite d'octets, et une colonne bit varying s'y lit comme le texte de ses chiffres.

30.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 SCHEMA public rend 42P06 (public désigne le schéma courant, il existe toujours ; IF NOT EXISTS : sans effet). Les éléments de schéma écrits à la suite (CREATE SCHEMA s CREATE TABLE … CREATE VIEW … CREATE INDEX … CREATE SEQUENCE … CREATE TRIGGER … GRANT …) sont créés dans le schéma, tout ou rien ; tout autre élément est refusé (0A000). AUTHORIZATION compte accorde au compte tous les privilèges sur le schéma (MIRAJ n'a pas de propriétaire) ; sans nom, le schéma porte celui du compte.
• Séquences : DROP SEQUENCE d'une séquence citée par un DEFAULT nextval(…) est refusé (2BP01) sans CASCADE, qui retire ces valeurs par défaut ; ALTER SEQUENCE … INCREMENT BY (et les autres options) vaut dès le nextval suivant, qui rend la dernière valeur servie plus le nouveau pas.
• Index : un nom d'index écrit est unique dans son schéma (42P07 pour un second, comme pour toute relation de PostgreSQL ; l'index implicite d'une colonne UNIQUE, nommé comme la colonne, ne compte pas) ; un index créé sur la colonne fille d'une clé étrangère se retire comme un autre (la clé reste contrôlée).
• 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" sans effet (c'est la collation par défaut des textes, voir 30.3).
• DEFAULT 'texte'::character varying, DEFAULT now(), DEFAULT CURRENT_TIMESTAMP, DEFAULT 'a' || 'b' : la valeur est gardée en texte MIRAJ.
• DEFAULT ma_fonction() (fonction stockée, seule ou dans une expression) : fonction résolue à la création (42883 si elle n'existe pas), exécutée pour chaque ligne écrite qui ne fournit pas la colonne (INSERT d'une ou plusieurs lignes, COPY, UPDATE … SET c = DEFAULT, ON CONFLICT DO UPDATE SET c = DEFAULT, lignes existantes d'un ALTER TABLE … ADD COLUMN), en lecture seule (une fonction qui écrit : 0A000), les tables qu'elle lit verrouillées avec l'instruction ; DROP FUNCTION refusé (2BP01) sans CASCADE, qui retire ces valeurs par défaut. Le langage MIRAJ refuse une fonction stockée en valeur par défaut (3770).
• 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 continue, sauf RESTART IDENTITY ; dans un bloc, les tables sont verrouillées en ACCESS EXCLUSIVE jusqu'à la fin de la transaction et vidées en bloc sous elle (ROLLBACK et ROLLBACK TO SAVEPOINT rendent les lignes, avec le compteur), table disque et table partitionnée de l'édition Cluster comprises (lignes supprimées une à une pour une table partitionnée dont une partition est tenue par un autre nœud).
• CREATE TEMP TABLE … ON COMMIT DELETE ROWS : table temporaire vidée à chaque validation (ON COMMIT PRESERVE ROWS : le défaut) ; ON COMMIT DROP : table temporaire supprimée à la fin de la transaction, validée ou annulée (hors d'un bloc, à la fin de l'instruction).
• CREATE [OR REPLACE] VIEW, CREATE INDEX nom ON t (c) (et USING btree), DROP TABLE … CASCADE.
• CREATE INDEX … (c [ASC | DESC] [NULLS FIRST | NULLS LAST]) : ordre et place des NULL lus sans effet (l'index est croissant ; l'ordre d'un résultat reste celui que demande la requête).
• ALTER TABLE ONLY t …, ALTER TABLE t * … : comme ALTER TABLE t ….
• Comptes : options de rôle de PostgreSQL traduites dans les comptes, rôles et privilèges de MIRAJ — CREATE USER nom [WITH] PASSWORD '…', CREATE ROLE nom LOGIN PASSWORD '…' (compte nom@'%'), CREATE ROLE nom [NOLOGIN] (rôle), ALTER USER | ROLE nom [WITH] PASSWORD '…', LOGIN / NOLOGIN (ACCOUNT UNLOCK | LOCK), IN ROLE (GRANT rôle TO nom), SUPERUSER (GRANT ALL ON *.* … WITH GRANT OPTION), CREATEDB (GRANT CREATE ON *.*), CREATEROLE (GRANT CREATE USER ON *.*), ALTER … NOSUPERUSER | NOCREATEDB | NOCREATEROLE (le REVOKE … ON *.* correspondant), ALTER ROLE r LOGIN (le rôle devient un compte), DROP ROLE d'un compte comme d'un rôle ; une option sans équivalent (VALID UNTIL 'date', CONNECTION LIMIT n, NOINHERIT, REPLICATION, BYPASSRLS, mot de passe déjà haché, ALTER ROLE … RENAME TO, … SET paramètre) est refusée (0A000, option nommée), jamais ignorée. Détail : 39.2 du manuel PostgreSQL.

30.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 16.10.4).

30.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 (v [classe]) : vector_l2_ops par défaut, vector_cosine_ops, vector_ip_ops), 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 (la session fermée reçoit l'erreur fatale 57P01 de PostgreSQL avant la fin de sa connexion ; 57P05 au délai d'inactivité), 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.

30.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.

30.8 Pas encore pris en charge#

30.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).

Vues sur le catalogue : une vue peut lire pg_catalog et information_schema (CREATE VIEW v AS SELECT relname FROM pg_class…), seule, jointe à des tables, au travers d'une autre vue ou d'une expression WITH ; pg_get_viewdef, pg_attribute et information_schema.columns la décrivent (colonnes du type de celles du catalogue : name, oid, "char"). Sa requête est rejouée à chaque lecture, pour le compte qui la lit : aucun résultat n'en est gardé, CREATE CACHED VIEW compris (le catalogue change sans qu'une table soit écrite, et chaque compte n'y voit que ses objets) ; REFRESH VIEW y est sans effet. Une vue sur pg_authid ou pg_shadow reste réservée à qui administre les comptes, à la création comme à la lecture (42501).

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. ::regproc ne connaît que les routines stockées, seules lignes de pg_proc : le nom d'une fonction du serveur ('length'::regproc) donne 42883, et to_regproc('length') NULL.

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. L'index au nom écrit l'emporte sur un index implicite de même nom ; un nom porté par deux tables au même titre (index créés en langage MIRAJ) est refusé comme ambigu (42710) : écrire DROP INDEX nom ON table.
• COMMENT ON porte sur une table ou une colonne ; un commentaire de colonne redéfinit la colonne (la table est réécrite). Sur un autre objet (COMMENT ON FUNCTION, ON INDEX…) : 0A000.

30.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. Les écritures au travers de la vue suivent aussi cette règle : dans un UPDATE ou un DELETE, la condition de la vue et ses colonnes calculées gardent le langage de la vue, tandis que ce que l'instruction écrit elle-même (SET, WHERE) suit le langage de l'instance. Avec une vue v créée en langage PostgreSQL par SELECT id, a, n, length(a) AS l FROM t WHERE strpos(a, 'x') = 0, l'instruction UPDATE v SET n = length(a) WHERE l = 3, exécutée dans l'instance rouverte en langage MIRAJ, ne touche que les lignes sans x de trois caractères (strpos et l de la vue), et y range un nombre d'octets (length de l'instruction). Il en va de même d'une vue qui lit une vue créée dans l'autre langage : chacune garde le sien.

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. La clause PARTITION BY d'une table partitionnée suit la définition, dans la syntaxe de MIRAJ (identifiants entre guillemets doubles, sans commentaire versionné ni mention du moteur). SHOW CREATE SEQUENCE, SHOW CREATE ENDPOINT, SHOW CREATE USER et SHOW GRANTS rendent leur texte avec la lexicographie de PostgreSQL (identifiants "…", chaînes standard ; le compte de SHOW CREATE USER s'écrit "app"@"%", comme celui de SHOW GRANTS) : ces lignes se rejouent telles quelles sur une instance PostgreSQL (miraj-dump --users). SHOW TRIGGERS et information_schema.triggers montrent un déclencheur écrit en PL/pgSQL sous son nom, avec EXECUTE FUNCTION f(…) pour action. Une routine ou un déclencheur écrit en PL/pgSQL est gardé en texte MIRAJ et son texte d'origine à côté (30.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).

30.11 Écritures : RETURNING, ON CONFLICT et COPY#

Formes de PostgreSQL admises dans les écritures :

• WITH … INSERT | UPDATE | DELETE : les expressions de table écrites en tête valent pour toute l'écriture (WITH RECURSIVE compris) ;
• expression de table modifiante, WITH x AS (INSERT | UPDATE | DELETE … [RETURNING …]) [, …] devant un SELECT, un INSERT, un UPDATE ou un DELETE : chaque écriture s'exécute une fois, dans l'ordre écrit, par son chemin ordinaire (droits, déclencheurs, journal) ; son nom désigne les lignes de son RETURNING, pour l'instruction principale et les écritures suivantes. Atomique hors d'un bloc de transaction (transaction implicite). Seulement dans la clause WITH qui ouvre l'instruction (sous-requête, vue, CREATE TABLE … AS, EXPLAIN, SELECT … INTO : 0A000) ; écart : l'instruction principale voit les tables après les écritures, pas leur état d'avant l'instruction ;
• INSERT INTO t VALUES (…) ou INSERT INTO t SELECT … sans liste de colonnes, avec moins de valeurs que de colonnes : elles vont aux premières colonnes, les autres prennent leur valeur par défaut ; trop de valeurs, ou des lignes de longueurs différentes : 42601 ;
• INSERT INTO t AS alias … : alias de la table visée, avec ou sans ON CONFLICT et RETURNING ;
• INSERT INTO t [(colonnes)] OVERRIDING {SYSTEM | USER} VALUE … : une colonne GENERATED ALWAYS AS IDENTITY n'admet que DEFAULT (INSERT, UPDATE, ON CONFLICT … DO UPDATE : 428C9 pour une autre valeur, NULL compris) ; OVERRIDING SYSTEM VALUE y écrit la valeur donnée, OVERRIDING USER VALUE ignore la valeur donnée à une colonne d'identité (pas à une colonne serial, qui n'en est pas une) ; COPY … FROM STDIN écrit les valeurs du fichier ; UPDATE … SET id = DEFAULT d'une colonne d'identité lui attribue le numéro suivant ; clause admise aussi dans le corps d'une routine PL/pgSQL ou SQL (le texte MIRAJ gardé ne la lit que dans ce corps traduit, bloc pg$body) ; le refus 428C9 porte sur le protocole PostgreSQL le détail et le conseil dans leurs champs (D, H) ;
• UPDATE ONLY t, DELETE FROM ONLY t, t * : sans effet.

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. SET (a, b) = (expr, expr) affecte plusieurs colonnes d'une liste de valeurs. Les déclencheurs UPDATE de la table s'exécutent, BEFORE et AFTER, même quand aucune valeur ne change ; un WHERE faux n'en lance aucun.
• 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) = (SELECT …) ; 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' et PROGRAM sont refusés (0A000) ; pour charger un fichier du serveur, LOAD DATA INFILE reste disponible en langage MIRAJ.
• COPY t | (requête) TO '/chemin' écrit les lignes, au même format que TO STDOUT, dans un fichier du serveur (étiquette COPY n, rien n'est envoyé au client), sous les garde-fous de SELECT … INTO OUTFILE : le serveur doit fixer un dossier --secure-file-priv (aucun par défaut : refus 42501, même pour root) ; le compte doit avoir le privilège FILE au niveau global (GRANT FILE ON *.* TO …), l'équivalent du rôle pg_write_server_files (sans lui : 42501 « permission denied to COPY to a file », comme PostgreSQL) ; le chemin est absolu (42602 sinon) et son dossier, rendu canonique (.. et liens résolus), se trouve dans secure_file_priv (42501 sinon). Écarts voulus : un fichier existant n'est jamais remplacé (58P02, PostgreSQL l'écrase), le fichier est créé en 0600 et retiré si l'instruction échoue.

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).

30.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 (30.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 (16.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 17.12.

30.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.

DO [LANGUAGE plpgsql] $$ … $$ exécute aussitôt un bloc PL/pgSQL anonyme, comme le corps d'une procédure sans paramètre, avec les droits de la session et dans la base courante, puis l'oublie : étiquette DO, avis de RAISE, erreur avec son SQLSTATE, bloc entier défait en cas d'erreur hors bloc de transaction du client ; LANGUAGE sql ou un autre langage : 0A000.

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.

Fonctions qui rendent des lignes#

RETURNS SETOF type (type de base ou table), RETURNS TABLE (colonne type, …) et les paramètres OUT avec RETURNS SETOF record s'appellent dans un FROM : SELECT * FROM f(1), jointures et sous-requêtes comprises, dans le corps d'une autre routine aussi. Le corps ajoute des lignes par RETURN NEXT expr (une colonne, ou un enregistrement de la table), RETURN NEXT sans expression (valeurs courantes des colonnes de RETURNS TABLE ou des paramètres OUT) et RETURN QUERY requête ; RETURN ou la fin du corps terminent le résultat. Une fonction LANGUAGE sql rend les lignes de sa dernière requête. Les valeurs sont converties aux types déclarés ; une requête dont le nombre de colonnes diffère : 42804, comme RETURN expr, et RETURN NEXT hors d'une fonction ensembliste.

Les lignes sont calculées avant l'instruction, gardées en mémoire. Les arguments doivent être constants (paramètres liés compris) : une colonne d'une autre table du FROM (LATERAL) est refusée (0A000), comme l'appel dans la liste d'un SELECT (SELECT f()), dans une vue, RETURN QUERY EXECUTE et RETURNS SETOF record sans paramètre OUT.

Refusé (0A000)#

Fonction scalaire à 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.

É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, 30.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 (routines stockées seulement, voir 30.9).
• 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 annule les écritures faites par le bloc avant l'erreur (point de sauvegarde implicite, comme PostgreSQL), sauf dans un déclencheur et pour une routine appelée hors transaction par une session en langage MIRAJ, où seule l'instruction en faute 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,18) (30.3) : la valeur rendue par une fonction (RETURNS numeric) s'écrit, comme une colonne numeric, sans les zéros finals de sa partie décimale.
• Les textes d'une routine ou d'un déclencheur (paramètres, variables, littéraux) sont en collation « C », comme ailleurs en langage PostgreSQL (30.3) : leurs comparaisons tiennent compte de la casse.
• 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.

30.14 Pilote ODBC et bibliothèque Python#

Le pilote MIRAJ ODBC Driver (chapitre 29) et la bibliothèque Python miraj (27.12) se connectent aussi à une instance PostgreSQL, sans changement d'API, par le même client PostgreSQL que les outils (30.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.