29. PostgreSQL language

An instance started with the POSTGRESQL element of sql_mode (see 15.10) speaks the PostgreSQL protocol and grammar: queries from psql, psycopg2, Npgsql or pgjdbc are written as for a PostgreSQL server. This chapter lists what this grammar accepts, the MIRAJ extensions that remain available in it, the MIRAJ-specific syntax that is rejected, and what is not yet supported.

The engine itself is the same: a PostgreSQL query is mapped to MIRAJ statements, with their rules (types, conversions, collations).

29.1 Lexical elements#

A quoted identifier remains case-insensitive: "Articles" and articles designate the same table (all MIRAJ names are case-insensitive). The name as written is kept as is at creation.

29.2 Expressions and queries#

Calculations and conversions follow the reference server, not the MIRAJ language:

• a division or modulo by zero (1 / 0, 5 % 0, x / 0.0) is a 22012 error (division_by_zero, which EXCEPTION WHEN division_by_zero catches), not a NULL;
• a text converted to a number ('abc'::int, CAST('1.5' AS integer), 'x'::numeric) that is not one gives 22P02 instead of its numeric prefix; an integer can be written 0x1F, 0o17, 0b101, 1_000; 'Infinity', '-Infinity' and 'NaN' are float8 values;
• a written integer that fits in 32 bits is an integer (pg_typeof(1), type announced to the client);
• sum of an integer is a bigint, sum of a bigint a numeric (no overflow); avg of integers or decimals is a numeric with at least 16 decimals (2.3333333333333333);
• stddev and variance are the sample standard deviation and variance (stddev_samp, var_samp); with stddev_pop and var_pop, they return on integers or decimals an exact numeric with at least 16 decimals (stddev of 1 to 5: 1.5811388300841897), a double precision on floats, and can also be used with OVER;
• x ^ y and power(x, y) return a double precision between integers or with a float, an exact numeric as soon as an operand is a decimal (2 ^ 0.5: 1.4142135623730950), with 16 significant digits and no fewer decimals than an operand; zero to a negative power or a negative to a non-integer power: 2201F;
• lower(1), upper(1.5): 42883, these functions existing only for text.

Values written into a column (INSERT, UPDATE): a boolean column reads 't', 'yes', 'on', '1', their opposites and prefixes (22P02 for 'maybe'); a uuid column keeps the canonical lowercase form (22P02 for a text that is not a UUID); a json or jsonb column rejects an unreadable document (22P02).

SELECT clauses:

• LIMIT n, LIMIT ALL, OFFSET n [ROWS] (alone or before LIMIT), FETCH {FIRST | NEXT} [n] {ROW | ROWS} ONLY;
• ORDER BY x [ASC | DESC] [NULLS FIRST | NULLS LAST] (MIRAJ sorts NULLs first in ascending order; the requested order is obtained by an added sort key), x possibly being an alias from the list; a jsonb column is sorted in PostgreSQL order (null < strings < numbers < booleans < arrays < objects);
• FOR UPDATE, FOR NO KEY UPDATE, FOR SHARE, FOR KEY SHARE, with NOWAIT or SKIP LOCKED.

Table 29.1. Conversion types#

MIRAJ names (SIGNED, DATETIME, BINARY…) are still accepted, and the pg_catalog. prefix is ignored.

29.3 Column types#

A serial column that is not by itself the primary key receives a uniqueness constraint (a MIRAJ AUTO_INCREMENT column must be a key).

A jsonb column behaves like a json column (same storage, same functions), but keeps its type: it is announced as jsonb (OID 3802) in RowDescription, pg_attribute.atttypid, format_type, pg_typeof and information_schema.columns.data_type, through a view or a derived table, and col -> 'key', col #> '{a,b}' on it return jsonb; a json column remains json (OID 114). ALTER COLUMN … TYPE jsonb (or json) changes the announced type. In the MIRAJ language, the column is written JSONB (see 5).

29.4 Data definition#

• CREATE SCHEMA [IF NOT EXISTS] name creates a database, DROP SCHEMA name [CASCADE | RESTRICT] drops it with all its contents: each MIRAJ database is a PostgreSQL schema.
• CREATE TEMP TABLE, CREATE TEMPORARY TABLE: session temporary table; CREATE UNLOGGED TABLE: ordinary table.
• Column constraints: NOT NULL, NULL, DEFAULT expr, PRIMARY KEY, UNIQUE, CHECK (…) (which may reference other columns), REFERENCES t (c), with or without a preceding CONSTRAINT name; DEFERRABLE, INITIALLY DEFERRED have no effect; COLLATE "C" ignored.
• DEFAULT 'text'::character varying, DEFAULT now(), DEFAULT CURRENT_TIMESTAMP, DEFAULT 'a' || 'b': the value is kept as MIRAJ text.
• GENERATED ALWAYS AS (expr) STORED: generated column.
• TRUNCATE [TABLE] [ONLY] t [RESTART IDENTITY | CONTINUE IDENTITY] [CASCADE | RESTRICT]: one table at a time; the auto-increment counter restarts at 1.
• CREATE [OR REPLACE] VIEW, CREATE INDEX name ON t (c) (and USING btree), DROP TABLE … CASCADE.

29.5 Session and transactions#

The parameters that PostgreSQL announces to clients (application_name, client_encoding, DateStyle, TimeZone, extra_float_digits…) are maintained by the network server (see 15.10.4).

29.6 Available MIRAJ extensions#

They are written with PostgreSQL lexical conventions (quoted identifiers, standard strings):

• vector indexes (CREATE VECTOR INDEX, CREATE INDEX … USING hnsw), distances <->, <#>, <+>; full-text indexes (CREATE FULLTEXT INDEX), MATCH (…) AGAINST (…);
• cached views (CREATE CACHED VIEW, REFRESH VIEW);
• REST endpoints (CREATE ENDPOINT, body limited to a single SELECT in the PostgreSQL language), change events (LISTEN, UNLISTEN, NOTIFY, WAIT FOR CHANGES);
• BACKUP DATABASE, RESTORE DATABASE, XA transactions (XA START…), KILL, USE;
• MIRAJ SHOW statements (SHOW TABLES, SHOW DATABASES, SHOW CREATE TABLE, SHOW PROCESSLIST, SHOW VARIABLES, SHOW CLUSTER STATUS…), @@name and @v variables;
• partitioning, temporal tables (FOR SYSTEM_TIME), QUALIFY, GROUP BY … WITH ROLLUP.

29.7 Rejected MIRAJ syntax#

These forms have a different meaning in PostgreSQL, or do not exist there: they receive a syntax error (42601) whose message names the construct.

29.8 Not yet supported#

29.9 The pg_catalog catalog#

PostgreSQL clients describe the database by reading the system tables (pg_class, pg_attribute…). An instance in the PostgreSQL language keeps them in a virtual database pg_catalog, built from the MIRAJ catalog on every statement that reads it, like information_schema: nothing is written to it (a write receives 42501), and an instance in the MIRAJ language does not see it (neither its tables nor the functions of this chapter).

Model: a single PostgreSQL database, miraj (pg_database); each MIRAJ database is a schema (pg_namespace). The current database is at the head of the search path: its tables are "visible" (pg_table_is_visible), those of other databases are named database.table. As in PostgreSQL, pg_catalog comes before any schema: SELECT * FROM pg_class reads the catalog table (a WITH expression of the same name takes precedence).

OIDs: base types keep those of PostgreSQL (int4 23, text 25, numeric 1700, arrays _int4 1007…), as do the catalog tables ('pg_class'::regclass is 1259). MIRAJ objects receive a synthetic, stable OID: a hash of their kind, database and name, greater than 16 384, which does not change from one restart to the next (renaming an object changes its OID). The vector type is that of the pgvector extension (OID 16385, extension vector in pg_extension).

Table 29.2. pg_catalog tables#

The columns are those of PostgreSQL 16, in their order, with their type (oid, name, "char", int2, bool…); catalog arrays (conkey, proargnames, indkey…) are written as text, {1,3} or 1 3 for int2vector.

PostgreSQL functions#

They exist only in the PostgreSQL language, and take precedence over a MIRAJ function of the same name (version(), length()…).

Conversions: 'clients'::regclass gives the OID of the table (42P01 if it does not exist), c.oid::regclass its name (qualified by its database outside the current database); likewise ::regtype (format_type), ::regproc, ::regnamespace, ::regrole.

Forms recognized for catalog queries: array[n] (i.indkey[0], (current_schemas(true))[1]), x = ANY (array) and x <> ALL (array) on a catalog array, generate_series(start, end [, step]) in a FROM (with AS s(n)), column aliases of a derived table ((SELECT …) AS x(a, b)), an alias after AS that is a reserved word (AS default). A condition (a = b, x IS NULL, EXISTS (…)…) placed in the list of a SELECT is a boolean (t / f).

Some fixed client queries use forms that the engine does not execute (functions that return rows such as information_schema._pg_expandarray, pg_partition_ancestors, arrays built by a correlated ARRAY(SELECT …)): the server recognizes their text (psql 10 to 18, pgjdbc 42.x, Npgsql 6 to 10) and rewrites them into equivalent queries on the same catalog.

Definitions and comments#

COMMENT ON TABLE clients IS 'Les clients';
COMMENT ON COLUMN clients.nom IS 'Nom complet';    -- IS NULL removes the comment
CREATE TABLE lignes (id serial PRIMARY KEY, commande bigint REFERENCES commandes, qte int);
CREATE INDEX lignes_qte ON lignes (qte);
DROP INDEX lignes_qte;                              -- without a table, like PostgreSQL
SHOW ALL;
SHOW statement_timeout;

• REFERENCES t without columns designates the primary key of t (42830 if it has none).
• DROP INDEX [IF EXISTS] [database.]name looks for the table that carries the index in the current database (or the one written); one index per statement.
• COMMENT ON applies to a table or a column; a column comment redefines the column (the table is rewritten).

29.10 Kept definitions and language switching#

A view, a default value, a CHECK constraint, a generated column, a partitioning, a REST endpoint or a LISTEN filter written in the PostgreSQL language are kept as MIRAJ text by the catalog (the query is rewritten from its tree). An instance can therefore change language (stop, change sql_mode, restart): its objects remain readable and usable in the other language.

Expressions kept with a table (generated column, expression index, default value, CHECK constraint) also keep the language of their creation: they are always computed according to its rules, whatever the language of the instance that writes or reads the table. A column GENERATED ALWAYS AS (split_part(code, '-', 1)) STORED or an index on (doc ->> 'k') created in the PostgreSQL language give the same value in an instance reopened in the MIRAJ language (split_part, strpos, jsonb_typeof… are still found there, length counts characters, ->> returns NULL for null), and a table created in the MIRAJ language keeps MIRAJ rules. The MIRAJ language itself does not gain the PostgreSQL functions: a query written in the MIRAJ language cannot call split_part (1305).

The partitioning function (and subpartitioning function) and the query of a view follow the same rule: a PARTITION BY LIST (length(a)) table created in the PostgreSQL language stores its rows by number of characters in any instance (including ADD / REORGANIZE PARTITION), and a view created in the PostgreSQL language is read in that language — its WITH CHECK OPTION condition too —, a view created in the MIRAJ language in that of MIRAJ. Only an UPDATE or a DELETE through a view, rewritten into a statement on the table, compiles the view's condition in the language of the session: a function specific to the other language is rejected there (1305).

Display follows the language of the instance: SHOW CREATE TABLE, SHOW CREATE VIEW and information_schema.VIEWS.VIEW_DEFINITION return, in the PostgreSQL language, a definition written in PostgreSQL (quoted identifiers, ||, LIMIT ALL, PostgreSQL types, indexes as CREATE INDEX), whatever the language in which the object was created. A BOOLEAN column (kept as TINYINT(1)) is written boolean there; ENUM(…) and SET(…) remain written as MIRAJ reads them (extension, values as standard strings), so that the text recreates the same column. SHOW CREATE SEQUENCE, SHOW CREATE ENDPOINT, SHOW CREATE USER and SHOW GRANTS return their text with PostgreSQL lexical conventions ("…" identifiers, standard strings): these lines can be replayed as is on a PostgreSQL instance (miraj-dump --users). A routine or trigger written in PL/pgSQL is kept as MIRAJ text with its original text alongside (29.13); written in the MIRAJ language, its body remains displayed in the MIRAJ language, like that of an event.

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

A computed column without an alias keeps its written name (SELECT a + 1 FROM t names the column a + 1).

29.11 Writes: RETURNING, ON CONFLICT and COPY#

RETURNING#

INSERT, UPDATE and DELETE accept a RETURNING clause, written like the list of a SELECT (*, t.*, expressions, aliases): the statement returns one row per written row, in the order of the writes, then its usual tag (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 and UPDATE return the values after the write: auto-increment value (SERIAL, identity), DEFAULT, generated columns, and what a BEFORE trigger changed; DELETE returns the values from before the deletion. The returned rows are read in the same transaction as the write, locks still held.
• Expressions are checked before any write (an unknown column, 42703, modifies nothing); a subquery in the clause sees the state from before the statement, like PostgreSQL.
• Extended protocol: Describe of the statement describes the columns of the clause (RowDescription), which is what pgjdbc's getGeneratedKeys and Npgsql's ExecuteReader expect.
• Privileges: SELECT on the table, in addition to the write privilege.
• An UPDATE … RETURNING is never deferred (deferred_update): its row is written right away so it can be read back.
• Not supported (0A000): through a view, on a partitioned table (Cluster edition), in a multi-table UPDATE or DELETE (DELETE … USING), subquery correlated to the written row.

RETURNING exists only in the PostgreSQL language: the MIRAJ language is unchanged.

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;

• The target designates the arbiter uniqueness constraint: a list of columns (the constraint whose columns are exactly these, otherwise 42P10) or ON CONSTRAINT name (primary key named <table>_pkey as in pg_catalog, or the name of a UNIQUE constraint; unknown: 42704). Without a target (DO NOTHING only), any uniqueness constraint is an arbiter.
• Only the arbiter is handled: a row that violates another uniqueness constraint receives the 23505 error, as in PostgreSQL (whereas ON DUPLICATE KEY UPDATE reacts to any key).
• DO NOTHING discards the proposed row without a warning; other errors (NOT NULL, CHECK, foreign keys) remain errors.
• DO UPDATE SET col = expr, … modifies the existing row: a column alone or qualified by the table (or its alias, INSERT INTO t AS x) designates the existing row, EXCLUDED.col the proposed row (default values and BEFORE INSERT trigger included), DEFAULT the default value. WHERE condition (on the existing row and EXCLUDED) leaves the row as is when it is not true. The table's UPDATE triggers run.
• A row inserted or modified by the statement cannot be modified a second time by it (two proposed rows with the same key): 21000, nothing is written.
• Count: one per inserted or modified row (INSERT 0 2), none for DO NOTHING or a false WHERE; a conflicting row modified without a change of value is counted too.
• Not supported (0A000): a target made of expressions (ON CONFLICT (lower(code))), of a partial index (WHERE), with COLLATE or an operator class; DO UPDATE SET (a, b) = (…); through a view or on a partitioned table.

COPY#

Options: FORMAT text (default) or FORMAT csv, DELIMITER 'c', NULL 'text', HEADER (skipped when reading, written first when writing), QUOTE 'c', ESCAPE 'c' (CSV), ENCODING 'UTF8', FREEZE (no effect). The legacy form of the options is accepted (WITH DELIMITER AS '|' NULL AS '', WITH CSV HEADER), as sent by psycopg2 (copy_from, copy_to) and old scripts.

• Text format: fields separated by a tab, NULL written \N, escapes \t, \n, \r, \\, \b, \f, \v, octal (\101) and hexadecimal (\x41). CSV: comma, double quotes, "" for a quote inside a quoted field, NULL = unquoted empty field, "" = empty string; a quoted field may contain line breaks.
• Received values: UTF-8 text (22021 otherwise); booleans written like PostgreSQL (t, true, yes, on, 1…, otherwise 22P02); bytea in hexadecimal \x… or escaped form; date-time with offset (2026-10-06 10:00:00+02) loaded without the offset, like a literal; the other conversions are those of LOAD DATA in the session's sql_mode (strict mode: an invalid value is an error). Written values: those a SELECT returns as text (t/f, bytea as \x…, ISO dates).
• A row that does not have as many fields as columns: 22P04 (missing data for column "nom", extra data after last expected column), with the row as context (COPY t, line 3).
• COPY … FROM STDIN runs like an INSERT (INSERT privilege, checked before the client sends the data; triggers, constraints, foreign keys) and as a single statement: an error, a client abort (CopyFail, 57014) or a cancellation (Ctrl-C, 57014) write nothing. After an error in the middle of the data, the server reads and discards the rest until the end of the transfer, then returns the error. The server first reads the table's columns: the SELECT privilege is also required.
• COPY … TO STDOUT requires the SELECT privilege, like the corresponding query.
• Without a column list, generated columns are excluded, in both directions.
• psql: \copy t from 'file.csv' with (format csv, header) and \copy t to 'output.txt' read and write the file on the client's machine, through COPY … FROM STDIN / TO STDOUT.
• Not supported (0A000): FORMAT binary, HEADER MATCH, FORCE_QUOTE, FORCE_NOT_NULL, FORCE_NULL, DEFAULT, ON_ERROR, COPY … FROM … WHERE, an encoding other than UTF-8, and COPY in the extended protocol (prepared statement). Server files: COPY t FROM '/path', COPY t TO '/path' and PROGRAM are rejected (0A000); to load or write a server file, LOAD DATA INFILE and SELECT … INTO OUTFILE remain available in the MIRAJ language, subject to secure_file_priv and the FILE privilege.

The data of a COPY … FROM STDIN is kept in memory until the end of the transfer, then loaded through the LOAD DATA LOCAL INFILE path (no temporary file).

29.12 MIRAJ tools on a PostgreSQL instance#

MIRAJ tools connect to both languages through the same client (miraj-connect): they detect the instance's language at connection time and send it their statements in that language.

Protocol selection: --protocol auto | miraj | postgresql option of each tool (protocol key of the [monitor] table of proxy.toml), auto by default.

• auto first sends a PostgreSQL encryption request (SSLRequest, or GSSENCRequest with --no-ssl) and reads one byte. S or N: PostgreSQL instance, the exchange continues on the same connection (TLS, then SCRAM-SHA-256 or cleartext password). Any other byte is the first of the legacy protocol's greeting: the connection is closed and reopened in the MIRAJ language. The MIRAJ server recognizes this probe and closes it without counting it in Aborted_connects (one "PostgreSQL client on a MIRAJ-language instance" line in its log).
• The detected language is remembered per endpoint (host:port) for the lifetime of the process: one extra connection, once, towards a MIRAJ instance.
• miraj and postgresql force the protocol (a legacy client on a PostgreSQL instance waits for the server's connection timeout before being closed).
• TLS options are the same in both protocols (--ssl, --ssl-ca, --ssl-insecure, --no-ssl); the proxy relays the probe like any other bytes.

Export of a PostgreSQL instance (miraj-dump):

• tables through SHOW CREATE TABLE (already in PostgreSQL), rows as multi-row INSERT: "…" identifiers, standard strings, booleans true/false, bytea as '\x…'::bytea, floats at shortest representation (-1.5e300); sequences, views, REST endpoints, accounts and privileges in the same lexical conventions, without DELIMITER;
• at the top: SET client_encoding = 'UTF8' and SET foreign_key_checks = 0; reading is done in a REPEATABLE READ, READ ONLY transaction;
• procedures, functions and triggers written in PL/pgSQL are written in that language (29.13); those written in the MIRAJ language (created before a language change) are translated to PL/pgSQL on a best-effort basis, otherwise quoted as a comment with the reason, like events;
• --users: the password is carried over as its hash (IDENTIFIED BY PASSWORD '*…'). A reloaded account has no SCRAM-SHA-256 verifier: it connects with a cleartext password (over TLS or on the loopback) until its password is reset (ALTER USER … IDENTIFIED BY …), like an account created before SCRAM (15.10.2). The tool reminds you of this;
• an export from a MIRAJ instance cannot be reloaded as is into a PostgreSQL instance, nor the reverse: the export follows the language of the instance read.

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

(on Windows, PGCLIENTENCODING=UTF8 or chcp 65001 for accented text.)

Cluster: all the nodes of a cluster have the same language. A node of another language is rejected on joining (error 9054, logged by both nodes): see 16.12.

29.13 Routines and triggers (PL/pgSQL)#

An instance in the PostgreSQL language accepts CREATE FUNCTION and CREATE PROCEDURE written in LANGUAGE plpgsql or LANGUAGE sql, and triggers CREATE TRIGGER … EXECUTE FUNCTION f(). They are translated at creation into MIRAJ routines and triggers, executed by the same interpreter as those written in the MIRAJ language. Whatever falls outside the subset described here is rejected at creation (0A000, the construct is named in the message): nothing is stored incorrectly.

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, with the notice "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);                   -- one row: 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();

CREATE FUNCTION and CREATE PROCEDURE header#

A LANGUAGE sql function returns the result of its last SELECT (first row, NULL with no row); its earlier statements are writes. A LANGUAGE sql procedure runs only writes.

PL/pgSQL subset#

Triggers#

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

• Function f is a RETURNS trigger function written in PL/pgSQL, without parameters. It reads NEW, OLD (NULL when the row does not exist: OLD of an INSERT, NEW of a DELETE), TG_OP, TG_NAME, TG_WHEN, TG_LEVEL, TG_TABLE_NAME, TG_RELNAME, TG_TABLE_SCHEMA, TG_NARGS, TG_ARGV[i]; it modifies NEW in a BEFORE trigger.
• RETURN NEW (or OLD for a DELETE) lets the row be written; RETURN NULL in a BEFORE trigger skips the row: it is neither inserted, modified nor deleted, is not counted, and the following triggers do not run. RETURN OLD of a BEFORE UPDATE keeps the old row. The value returned by an AFTER trigger is ignored. Reaching the end of the function without RETURN: 2F005.
• A single function can serve several triggers, on several tables; two tables may have a trigger of the same name. Replacing the function (CREATE OR REPLACE FUNCTION) updates the triggers that call it.
• The triggers of a table, for a given timing and event, run in alphabetical order of their names, as in PostgreSQL.
• Calling a trigger function directly (SELECT f()): 0A000.

A PostgreSQL trigger becomes a MIRAJ trigger per event, named name$table$event (set_updated_at$products$update), whose body is the translation of the function for that event. An instance in the MIRAJ language sees them under these names (SHOW TRIGGERS); in the PostgreSQL language, pg_trigger, \d table and DROP TRIGGER name ON table see them under their PostgreSQL name, one row per trigger.

Rejected (0A000)#

RETURNS SETOF, RETURNS TABLE, RETURN NEXT, RETURN QUERY (functions that return rows); function with several OUT parameters, parameter or result of composite type (RECORD, table); EXECUTE (dynamic SQL), FOR … IN EXECUTE; FOREACH and arrays; cursors with arguments, OPEN … FOR, refcursor, FETCH other than NEXT, MOVE; COMMIT / ROLLBACK in a procedure; VARIADIC, polymorphic types (anyelement…); INSERT … ON CONFLICT, UPDATE | DELETE … RETURNING, RETURNING of a row other than an INSERT … VALUES in the body; a SELECT without INTO in the body (42601, like PostgreSQL); other statements (DDL) in the body; LANGUAGE c, internal, plpython…; SET, WINDOW, TRANSFORM, SUPPORT clauses; BEGIN ATOMIC; FOR EACH STATEMENT triggers (or without FOR EACH ROW), INSTEAD OF, TRUNCATE, CONSTRAINT TRIGGER, REFERENCING; ALTER FUNCTION | PROCEDURE | TRIGGER; anonymous blocks DO $$ … $$.

Scheduled events (CREATE | ALTER EVENT): rejected in the PostgreSQL language. PostgreSQL has none (pg_cron is an extension) and a MIRAJ event written in PL/pgSQL would be invented syntax. Events created in the MIRAJ language, before a language change, continue to run.

Storage, display and language switching#

• The catalog keeps the MIRAJ text of the translation (like a view, 29.10): this is what runs, and an instance restarted in the MIRAJ language runs and displays (SHOW CREATE FUNCTION, in the MIRAJ language) the routines and triggers created in PL/pgSQL.
• The original text (CREATE FUNCTION … AS $$…$$, CREATE TRIGGER …) is kept alongside: display in the PostgreSQL language comes from it, character for character for the body (prosrc, pg_get_functiondef, \sf), and a replaced trigger function is retranslated from it. Back in the PostgreSQL language, the displayed definitions are the same as before the switch.
• A routine or trigger written in the MIRAJ language remains displayed in the MIRAJ language (SHOW CREATE, pg_get_functiondef).

SHOW CREATE FUNCTION | PROCEDURE returns, for a routine written in PostgreSQL, the text of pg_get_functiondef; SHOW CREATE TRIGGER name (PostgreSQL name or MIRAJ name) that of pg_get_triggerdef. Since MIRAJ applies the precision of a parameter or of the result (a returned value is converted to the declared type), the displayed header keeps it (numeric(10,2); PostgreSQL forgets it and writes numeric).

Catalog, psql and tools#

• pg_proc: prosrc (body as written), prolang (sql 14, plpgsql 13600, see 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, 'name'::regproc, 'name(types)'::regprocedure.
• pg_trigger: one row per PostgreSQL trigger (tgtype of its events, tgfoid, tgargs, tgattr, tgqual); pg_get_triggerdef(oid [, pretty]).
• psql 18: \df, \df+ (volatility, security, language), \sf, \sf+, \d table ("Triggers" section).
• miraj-dump writes routines and triggers as PL/pgSQL (CREATE OR REPLACE FUNCTION … AS $function$…$function$, DROP … CASCADE first; one trigger per name and per table, after the rows), without DELIMITER: the script reloads through psql -f and miraj-dump restore. A routine written in the MIRAJ language is translated there to PL/pgSQL on a best-effort basis, otherwise quoted as a comment.
• miraj-migrate towards an instance in the PostgreSQL language translates the source's routines and triggers to PL/pgSQL (17); whatever cannot be translated is quoted with a warning.

Differences#

• An EXCEPTION handler does not roll back the writes made by the block before the error (PostgreSQL rolls them back through an implicit savepoint); the failing statement itself is rolled back. The chosen handler is the most specific one that covers the error (number, SQLSTATE, then OTHERS), not the first one written.
• A function cannot call itself, directly or indirectly (1424: recursion rejected, as for MIRAJ routines).
• UPDATE OF col fires when the value of one of those columns changes (case included), not when the column is merely named by SET without changing.
• %TYPE / %ROWTYPE types and the fields of a RECORD are resolved at creation: the tables (and the queries of a FOR r IN query) must exist; a table modified afterwards is not re-read (recreate the routine). Likewise, TG_TABLE_NAME is fixed when the trigger is created.
• numeric without precision is numeric(38,10) (29.3): a result is displayed with ten decimals.
• Text comparisons ignore case (MIRAJ collations), in the body as elsewhere.
• PostgreSQL-specific functions (pg_sleep, format…) used in a body run only on an instance in the PostgreSQL language; an instance switched back to the MIRAJ language rejects them at execution.
• A RAISE notice does not carry up the DETAIL / HINT fields.
• A routine's variables mask a column of the same name in its queries (PostgreSQL reports the ambiguity).
• No comparison with a reference PostgreSQL server for this corpus (the local server could not be reached without changing its configuration): the behaviors described follow the PostgreSQL 16 documentation.

29.14 ODBC driver and Python library#

The MIRAJ ODBC Driver (chapter 28) and the Python miraj library (26.12) also connect to a PostgreSQL instance, without any API change, through the same PostgreSQL client as the tools (29.12):

• Protocol selection: PROTOCOL keyword of the connection string or ODBC data source, protocol parameter of miraj.connect(); auto (default), miraj or postgresql, with the same detection as the tools.
• Parameters: the application's markers (? in ODBC, %s / %(name)s in Python) become $1, $2… of a named server-side prepared statement, reused (repeated SQLPrepare then SQLExecute, executemany); values sent as text with their type.
• Results: columns described with their PostgreSQL type, precision and scale, their source table and column, their nullability and auto-increment (read from pg_attribute); SQL_ATTR_MAX_ROWS limits the rows sent by the server.
• ODBC: SQLGetInfo announces PostgreSQL and the " quote; catalog functions return TABLE_CAT = miraj and the MIRAJ database as TABLE_SCHEM; SQLCancel sends a cancel request (CancelRequest), the statement fails with HY008.
• Python: mogrify writes the literals of this language; copy_from, copy_to and copy_expert go through COPY … FROM STDIN / TO STDOUT; bool and json decoded, vector as text [x,y,…]; lastrowid is None (INSERT … RETURNING).
• Errors: PostgreSQL SQLSTATE, with no MIRAJ code.

Third-party PostgreSQL drivers (psqlODBC, psycopg2/psycopg) also work.