30. PostgreSQL language

An instance started with the POSTGRESQL element of sql_mode (see 16.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.

This chapter is a condensed list. The PostgreSQL language manual describes the same language in detail, chapter by chapter (getting started, types, DDL, DML, queries, functions, transactions, accounts, PL/pgSQL, catalog and tools), with complete examples.

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

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

A column name (or an index name, or an alias) accepts non-ASCII letters and digits. Written without quotes, a name that carries a non-ASCII letter is folded to lowercase, like PostgreSQL in UTF-8 (PRÉNOM names prénom); between quotes it is kept, and the case of its non-ASCII letters matters ("Âge" is not âge). The names of tables, views, sequences, schemas and routines remain ASCII (42000), as does any name that carries a combining mark or punctuation.

30.2 Expressions and queries#

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

• / between two integers is an integer division truncated toward zero (7 / 2 is 3, -7 / 2 is -3); with a numeric, the quotient has PostgreSQL's scale (7 / 2.0 is 3.5000000000000000);
• a division or modulo by zero (1 / 0, 5 % 0, x / 0.0, mod(x, 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; the text of an integer can be written '0x1F', '0o17', '0b101', '1_000' ('0x1F'::int is 31), like the number written without quotes: 0x1F, 0o17, 0b101 and 1_000 are integers there (31, 15, 5 and 1000, not a byte sequence as in the MIRAJ language), the underscore also separates the digits of a decimal or of an exponent (1_000.5, 1e1_0), an invalid form (1__0, 1_, 0x) is a syntax error (42601); '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;
• trim, btrim, ltrim, rtrim: for text and for bytea only (trim(42): 42883, function btrim(integer) does not exist); on a bytea (btrim(bytes, bytes), trim(LEADING bytes FROM bytes)), bytes are removed and the result is a bytea;
• wrong number of arguments: 42883, with PostgreSQL's message (function btrim(unknown, unknown, unknown) does not exist).

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); a jsonb column stores the normalized document (see 30.3); a bpchar column without a length stores the text without its trailing spaces. A default value written as a string is read the same way in the column's type: boolean DEFAULT 'yes', jsonb DEFAULT '{"b": 1, "a": 2}' (normalized, read back as '…'::jsonb).

Calls and precedence:

• :: binds tighter than the unary minus: -3.7::int is -(3.7::int), that is -4 (the same in the MIRAJ language, where :: is an extension);
• named arguments of a stored function, f(a => 1, b => 2) or f(a := 1), after any positional arguments; an omitted parameter takes its default value (unknown name or name given twice: 42883), likewise in CALL p(a => 1); rejected for a server function (0A000);
• quoted function name, with or without a schema: "f"(1);
• lastval(): last value served to the session by nextval or by a serial / IDENTITY column (55000 if none); currval('s') before any nextval('s') of the session: 55000 too (NULL in the MIRAJ language);
• function in a FROM, SELECT * FROM f(…) [[AS] alias [(column)]] (what psycopg2's cursor.callproc sends): one row of one column, named by the alias, otherwise by the function; it sees the tables that precede it (LATERAL allowed before it, in first position too). Several rows: generate_series, unnest (ARRAY[a, b, …], array as text '{1,2}'::int[], bound parameter), regexp_matches, regexp_split_to_table, json[b]_each, json[b]_each_text, json[b]_array_elements[_text] and json[b]_object_keys (these JSON functions may cite the tables that precede them; the bounds of generate_series may not: 42703), with WITH ORDINALITY [AS alias (column, rank)], and set-returning stored functions (RETURNS SETOF, RETURNS TABLE, see 30.13 "Functions that return rows") with constant arguments (definition list AS t (a int, …), WITH ORDINALITY after a stored function: 0A000);
• set-returning functions in the SELECT list (SELECT generate_series(1, 3), unnest(ARRAY[…]), regexp_matches, regexp_split_to_table, json[b]_array_elements[_text], json[b]_object_keys): one row per value, like PostgreSQL 10 and later; several functions in the same list advance together, the longest one setting the number of rows; not in a grouped, aggregated or windowed list (0A000);
• column aliases AS x (a, b) after any FROM source (table, view, WITH expression, derived table, VALUES, function): the first columns are renamed, the following ones keep their name; too many aliases: 42P10;
• arrays in an expression: ARRAY[…], '{…}'::type[], array[n], array_agg, rendered in PostgreSQL's text form ({1,2,NULL}) and announced under the array type of their elements (int4[], text[]…); a column cannot be of an array type (30.8);
• ONLY table, ONLY (table) and table * in FROM, UPDATE, DELETE, TRUNCATE, LOCK and ALTER TABLE (the pg_dump form): no effect, MIRAJ having no table inheritance;
• GROUP BY on the whole integer primary key of a table: its other columns are allowed outside an aggregate (functional dependency), t.* included; not for a view, a derived table, a UNIQUE constraint or a key cited in part (42803).

SELECT clauses:

• SELECT DISTINCT ON (expr, …): first row of each group, in the order of the ORDER BY, which must start with these expressions (42P10 otherwise);
• LIMIT n, LIMIT ALL, OFFSET n [ROWS] (alone or before LIMIT), FETCH {FIRST | NEXT} [n] {ROW | ROWS} {ONLY | WITH TIES} (WITH TIES without an ORDER BY at the same level: 42601);
• 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.

30.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). Like a PostgreSQL sequence, its counter does not advance for a written value, and a number drawn by an insert that fails (23505) or that a ROLLBACK cancels is consumed.

"C" collation. Texts are compared and sorted byte by byte, like PostgreSQL with C.UTF-8 (SHOW lc_collate: C): a column created without COLLATE in a database with no collation of its own is utf8mb4_bin, and so are the connection (collation_connection), ::text conversions and the texts of PL/pgSQL or SQL routines. 'a' and 'A' are therefore two distinct values of a primary key, a UNIQUE constraint, a GROUP BY or a DISTINCT, and ORDER BY places uppercase first; only trailing spaces are ignored by =. ILIKE and ~* ignore case. A written MIRAJ collation (COLLATE utf8mb4_general_ci, on a column or in an expression) is still applied. Tables, routines and triggers created before this collation was adopted, and the catalog columns (except pg_settings), remain utf8mb4_general_ci, case-insensitive.

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

A jsonb is normalized as in PostgreSQL, when written into a jsonb column (INSERT, UPDATE, COPY), by ::jsonb and in the result of the functions that return a jsonb: in each object, a repeated key keeps only its last value and keys are sorted by length then in binary order; spacing is canonical ({"a": 1, "b": [1, 2]}) and a number written with an exponent is expanded (1e2: 100). '{"a":1,"b":2}'::jsonb = '{"b":2,"a":1}'::jsonb is therefore true, and an object or array written as text against a jsonb (doc = '{"b":2,"a":1}', doc || '{"c": 3}') is read as jsonb. A json keeps the text as written. Two jsonb values are compared by value, in the type's own order (null, strings, numbers, booleans, arrays, objects; numbers by their value): '1.0'::jsonb = '1'::jsonb is true, '2'::jsonb < '10'::jsonb too, and ORDER BY, DISTINCT, GROUP BY, UNION, IN, BETWEEN and a join key follow this comparison, as do a primary key and a UNIQUE constraint (1.0 and 1 are a duplicate there). ||, -, #- and jsonb_set exist only for a jsonb: on a json, they are rejected (42883). A document stored before this normalization, or written in the MIRAJ language (where the column behaves like a JSON and is compared by its text), keeps its text until its next write in the PostgreSQL language; UPDATE t SET doc = doc::jsonb normalizes it.

An ENUM column is announced to the client under the OID of its enumerated type <table>_<column>_enum, the one in pg_attribute; this type follows the value through a view, a derived table, CASE, coalesce and max / min (RowDescription, pg_attribute of a view, pg_typeof).

A table column taken as is is announced in RowDescription (simple query and Describe) under its declared type, with its modifier, the OID of its table and its column number: in direct reading, after DISTINCT, after GROUP BY (grouping key, column that the grouped primary key determines, *), through a derived table or a table expression; through a view, the announced relation is the view. UNION, INTERSECT and EXCEPT keep the type and modifier shared by all their members, with no relation; varchar(n) of different lengths, or a varchar(n) united with an untyped written text, are announced varchar with no modifier, char(n) of different lengths bpchar with no modifier. pg_typeof names the declared type of a column after GROUP BY as before. The MIRAJ language still announces no source table after an aggregation or a union.

A view column is announced as the query that defines the view announces it when it is run directly (type, modifier, pg_typeof; the announced relation is the view), whether the view's stored result is served or its query replayed, whatever the session time zone; pg_attribute and information_schema.columns describe the view the same way. A computed column follows the type of its expression (v || 'z': text), a written conversion keeps its type (::int2, ::real, ::varchar(5), ::char(3), ::timestamptz(0)…, which pg_get_viewdef renders under its name), a view read by another view keeps the declared type of the base column. The modifier of a written conversion (::numeric(10,2), ::varchar(5)) is not announced (PostgreSQL announces it), in a query as through a view; that of a ::char(n) is.

A value announced character(n) (char(n) column read directly, after GROUP BY or DISTINCT, through a view, a derived table or a CTE; ::char(n) conversion) is sent to the client padded with spaces up to n characters, in text, in binary and by COPY … TO STDOUT, as PostgreSQL displays it. The engine stores and computes it without its trailing spaces: length, ||, ::text and comparisons ignore them ('ab '::char(5) || '|' is ab|), octet_length and LIKE / ILIKE read them (octet_length('ab'::char(5)) is 5, 'ab'::char(5) LIKE 'ab' is false, LIKE 'ab ' and LIKE 'ab%' are true). The embedded interface returns the value without padding; max, coalesce and CASE of a char(n) are announced text, without padding; concat, format, to_json, array_agg and ~ read the value without its padding spaces. A bpchar without a length is never padded. The MIRAJ language always reads a CHAR(n) without trailing spaces.

A bit(n) column is announced bit (OID 1560, modifier n) and its values are written as binary digits over n positions, most significant first (101, 00000101 for a bit(8)), in a result as in COPY … TO. It receives a string of n binary digits ('101', B'101', X'1F', or hexadecimal 'x1F'; another number of positions is rejected, SQLSTATE 22026), as a written value, as a parameter or by COPY … FROM, or an integer that fits in n positions (22026 otherwise). A bit varying(n) column keeps at most n digits (22001 beyond that) and is announced bit varying (OID 1562).

In an expression, a bit string is the text of its digits, announced bit or bit varying: literals B'101' and X'1F', conversions ::bit(n), ::bit varying(n), ::integer and ::text, operators &, |, #, ~, <<, >> (same length required for the first three, 22026) and ||, functions length, bit_length, octet_length, bit_count, get_bit, set_bit, substring, position, overlay. max, min, coalesce, nullif, CASE, greatest, a union, a view, a derived table and pg_typeof keep the type; a bit column is compared with a bit string or with a text read the same way (b = '101'), position by position. A bit(n) column read alone, sorted, grouped or compared with a string of n positions remains the integer that the engine stores (indexes are used); a computation reads it over its n positions. The canonical text of a view or a generated column keeps these forms as internal calls (pg_bit('101'), pg_bit_cast(x, n)), read back by both languages and redisplayed as B'101', cast(x as bit(n)) in the PostgreSQL language.

A bit string enters only a bit(n) or bit varying column: any other column (bytea, text, integer, numeric, boolean, date, jsonb, uuid…) rejects it (42804, column "b" is of type bytea but expression is of type bit) through INSERT … VALUES, INSERT … SELECT, UPDATE, ON CONFLICT DO UPDATE and as a default value; it must be converted (b::text, b::integer). In particular X'DEADBEEF' is not a byte sequence in the PostgreSQL language: a bytea is written '\xDEADBEEF' or decode('DEADBEEF', 'hex').

The default value of a bytea column (DEFAULT '\x0aff'::bytea, DEFAULT '\x0aff') is kept as a byte sequence ((X'0AFF') in MIRAJ text, '\x0aff'::bytea when displayed in the PostgreSQL language); a table created before this rule keeps its default as it was stored, until ALTER TABLE … ALTER COLUMN … SET DEFAULT.

Differences: a bit(n) has at most 64 positions; an integer is still accepted in a bit(n) column and is compared there (b = 5); a computed bit string kept by CREATE TABLE … AS SELECT or by a view makes a bit varying column; bit_and, bit_or, bit_xor and to_json of a bit(n) column return a number; a bit varying column receives only a bit string (a number, or a bit(n) column read without conversion by INSERT … SELECT, is rejected: 42804). In the MIRAJ language, nothing changes: BIT(n) is an integer there, X'0F' a byte sequence, and a bit varying column is read there as the text of its digits.

30.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 SCHEMA public returns 42P06 (public designates the current schema, which always exists; IF NOT EXISTS: no effect). Schema elements written after it (CREATE SCHEMA s CREATE TABLE … CREATE VIEW … CREATE INDEX … CREATE SEQUENCE … CREATE TRIGGER … GRANT …) are created in the schema, all or nothing; any other element is rejected (0A000). AUTHORIZATION account grants the account all privileges on the schema (MIRAJ has no owner); without a name, the schema takes the account's name.
• Sequences: DROP SEQUENCE of a sequence cited by a DEFAULT nextval(…) is rejected (2BP01) without CASCADE, which removes these default values; ALTER SEQUENCE … INCREMENT BY (and the other options) applies from the next nextval, which returns the last value served plus the new step.
• Indexes: a written index name is unique in its schema (42P07 for a second one, as for any PostgreSQL relation; the implicit index of a UNIQUE column, named like the column, does not count); an index created on the child column of a foreign key is dropped like any other (the key remains enforced).
• 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" has no effect (it is the default collation of texts, see 30.3).
• 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.
• DEFAULT my_function() (stored function, alone or in an expression): function resolved at creation (42883 if it does not exist), run for each written row that does not supply the column (INSERT of one or several rows, COPY, UPDATE … SET c = DEFAULT, ON CONFLICT DO UPDATE SET c = DEFAULT, existing rows of an ALTER TABLE … ADD COLUMN), read-only (a function that writes: 0A000), the tables it reads locked with the statement; DROP FUNCTION rejected (2BP01) without CASCADE, which removes these default values. The MIRAJ language rejects a stored function as a default value (3770).
• TRUNCATE [TABLE] [ONLY] t [RESTART IDENTITY | CONTINUE IDENTITY] [CASCADE | RESTRICT]: one table at a time; the auto-increment counter continues, except with RESTART IDENTITY; in a block, the tables are locked ACCESS EXCLUSIVE until the end of the transaction and emptied in bulk under it (ROLLBACK and ROLLBACK TO SAVEPOINT give the rows back, with the counter), on-disk tables and partitioned tables of the Cluster edition included (rows deleted one by one for a partitioned table one of whose partitions is held by another node).
• CREATE TEMP TABLE … ON COMMIT DELETE ROWS: temporary table emptied at each commit (ON COMMIT PRESERVE ROWS: the default); ON COMMIT DROP: temporary table dropped at the end of the transaction, committed or rolled back (outside a block, at the end of the statement).
• CREATE [OR REPLACE] VIEW, CREATE INDEX name ON t (c) (and USING btree), DROP TABLE … CASCADE.
• CREATE INDEX … (c [ASC | DESC] [NULLS FIRST | NULLS LAST]): order and NULL placement read with no effect (the index is ascending; the order of a result remains the one the query asks for).
• ALTER TABLE ONLY t …, ALTER TABLE t * …: like ALTER TABLE t ….
• Accounts: PostgreSQL role options translated into MIRAJ accounts, roles and privileges — CREATE USER name [WITH] PASSWORD '…', CREATE ROLE name LOGIN PASSWORD '…' (account name@'%'), CREATE ROLE name [NOLOGIN] (role), ALTER USER | ROLE name [WITH] PASSWORD '…', LOGIN / NOLOGIN (ACCOUNT UNLOCK | LOCK), IN ROLE (GRANT role TO name), SUPERUSER (GRANT ALL ON *.* … WITH GRANT OPTION), CREATEDB (GRANT CREATE ON *.*), CREATEROLE (GRANT CREATE USER ON *.*), ALTER … NOSUPERUSER | NOCREATEDB | NOCREATEROLE (the matching REVOKE … ON *.*), ALTER ROLE r LOGIN (the role becomes an account), DROP ROLE of an account as of a role; an option with no equivalent (VALID UNTIL 'date', CONNECTION LIMIT n, NOINHERIT, REPLICATION, BYPASSRLS, an already hashed password, ALTER ROLE … RENAME TO, … SET parameter) is rejected (0A000, option named), never ignored. Details: 39.2 of the PostgreSQL manual.

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

30.6 Available MIRAJ extensions#

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

• vector indexes (CREATE VECTOR INDEX, CREATE INDEX … USING hnsw (v [class]): vector_l2_ops by default, vector_cosine_ops, vector_ip_ops), 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 (the closed session receives PostgreSQL's fatal error 57P01 before its connection ends; 57P05 on the idle timeout), 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.

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

30.8 Not yet supported#

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

Views on the catalog: a view may read pg_catalog and information_schema (CREATE VIEW v AS SELECT relname FROM pg_class…), alone, joined to tables, through another view or a WITH expression; pg_get_viewdef, pg_attribute and information_schema.columns describe it (columns of the catalog's types: name, oid, "char"). Its query is replayed at each read, for the account that reads it: no result is kept, CREATE CACHED VIEW included (the catalog changes without a table being written, and each account sees only its own objects there); REFRESH VIEW has no effect on it. A view on pg_authid or pg_shadow remains reserved to whoever administers accounts, at creation as at read time (42501).

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. ::regproc knows only stored routines, the only rows of pg_proc: the name of a server function ('length'::regproc) gives 42883, and to_regproc('length') NULL.

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. The index with the written name takes precedence over an implicit index of the same name; a name carried by two tables equally (indexes created in the MIRAJ language) is rejected as ambiguous (42710): write DROP INDEX name ON table.
• COMMENT ON applies to a table or a column; a column comment redefines the column (the table is rewritten). On another object (COMMENT ON FUNCTION, ON INDEX…): 0A000.

30.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. Writes through the view also follow this rule: in an UPDATE or a DELETE, the view's condition and its computed columns keep the view's language, while what the statement itself writes (SET, WHERE) follows the instance's language. With a view v created in the PostgreSQL language by SELECT id, a, n, length(a) AS l FROM t WHERE strpos(a, 'x') = 0, the statement UPDATE v SET n = length(a) WHERE l = 3, run in the instance reopened in the MIRAJ language, touches only the rows without x of three characters (strpos and l of the view), and stores there a number of bytes (length of the statement). The same goes for a view that reads a view created in the other language: each keeps its own.

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. The PARTITION BY clause of a partitioned table follows the definition, in MIRAJ syntax (identifiers between double quotes, no versioned comment or mention of the engine). SHOW CREATE SEQUENCE, SHOW CREATE ENDPOINT, SHOW CREATE USER and SHOW GRANTS return their text with PostgreSQL lexical conventions ("…" identifiers, standard strings; the account of SHOW CREATE USER is written "app"@"%", like that of SHOW GRANTS): these lines can be replayed as is on a PostgreSQL instance (miraj-dump --users). SHOW TRIGGERS and information_schema.triggers show a trigger written in PL/pgSQL under its name, with EXECUTE FUNCTION f(…) as its action. A routine or trigger written in PL/pgSQL is kept as MIRAJ text with its original text alongside (30.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).

30.11 Writes: RETURNING, ON CONFLICT and COPY#

PostgreSQL forms accepted in writes:

• WITH … INSERT | UPDATE | DELETE: the table expressions written at the head apply to the whole write (WITH RECURSIVE included);
• data-modifying table expression, WITH x AS (INSERT | UPDATE | DELETE … [RETURNING …]) [, …] in front of a SELECT, an INSERT, an UPDATE or a DELETE: each write runs once, in the written order, through its ordinary path (privileges, triggers, journal); its name designates the rows of its RETURNING, for the main statement and the following writes. Atomic outside a transaction block (implicit transaction). Only in the WITH clause that opens the statement (subquery, view, CREATE TABLE … AS, EXPLAIN, SELECT … INTO: 0A000); difference: the main statement sees the tables after the writes, not their state from before the statement;
• INSERT INTO t VALUES (…) or INSERT INTO t SELECT … without a column list, with fewer values than columns: they go to the first columns, the others take their default value; too many values, or rows of different lengths: 42601;
• INSERT INTO t AS alias …: alias of the target table, with or without ON CONFLICT and RETURNING;
• INSERT INTO t [(columns)] OVERRIDING {SYSTEM | USER} VALUE …: a GENERATED ALWAYS AS IDENTITY column accepts only DEFAULT (INSERT, UPDATE, ON CONFLICT … DO UPDATE: 428C9 for any other value, NULL included); OVERRIDING SYSTEM VALUE writes the given value there, OVERRIDING USER VALUE ignores the value given to an identity column (not to a serial column, which is not one); COPY … FROM STDIN writes the values of the file; UPDATE … SET id = DEFAULT of an identity column gives it the next number; clause also accepted in the body of a PL/pgSQL or SQL routine (the kept MIRAJ text reads it only in that translated body, pg$body block); the 428C9 rejection carries the detail and the hint in their fields (D, H) of the PostgreSQL protocol;
• UPDATE ONLY t, DELETE FROM ONLY t, t *: no effect.

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. SET (a, b) = (expr, expr) assigns several columns from a list of values. The table's UPDATE triggers run, BEFORE and AFTER, even when no value changes; a false WHERE fires none.
• 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) = (SELECT …); 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' and PROGRAM are rejected (0A000); to load a server file, LOAD DATA INFILE remains available in the MIRAJ language.
• COPY t | (query) TO '/path' writes the rows, in the same format as TO STDOUT, to a server file (tag COPY n, nothing is sent to the client), under the safeguards of SELECT … INTO OUTFILE: the server must set a --secure-file-priv folder (none by default: rejected with 42501, even for root); the account must have the FILE privilege at the global level (GRANT FILE ON *.* TO …), the equivalent of the pg_write_server_files role (without it: 42501 "permission denied to COPY to a file", like PostgreSQL); the path is absolute (42602 otherwise) and its folder, made canonical (.. and links resolved), lies within secure_file_priv (42501 otherwise). Intended differences: an existing file is never replaced (58P02, PostgreSQL overwrites it), the file is created with mode 0600 and removed if the statement fails.

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

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

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

DO [LANGUAGE plpgsql] $$ … $$ immediately runs an anonymous PL/pgSQL block, like the body of a procedure without parameters, with the session's rights and in the current database, then forgets it: tag DO, RAISE notices, error with its SQLSTATE, whole block undone on error outside a client transaction block; LANGUAGE sql or another language: 0A000.

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.

Functions that return rows#

RETURNS SETOF type (base type or table), RETURNS TABLE (column type, …) and OUT parameters with RETURNS SETOF record are called in a FROM: SELECT * FROM f(1), joins and subqueries included, in the body of another routine too. The body adds rows with RETURN NEXT expr (one column, or a record of the table), RETURN NEXT without an expression (current values of the RETURNS TABLE columns or of the OUT parameters) and RETURN QUERY query; RETURN or the end of the body ends the result. A LANGUAGE sql function returns the rows of its last query. Values are converted to the declared types; a query whose number of columns differs: 42804, like RETURN expr, and RETURN NEXT outside a set-returning function.

The rows are computed before the statement and kept in memory. Arguments must be constant (bound parameters included): a column of another table of the FROM (LATERAL) is rejected (0A000), as is the call in the list of a SELECT (SELECT f()), in a view, RETURN QUERY EXECUTE and RETURNS SETOF record without an OUT parameter.

Rejected (0A000)#

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

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, 30.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 (stored routines only, see 30.9).
• 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 rolls back the writes made by the block before the error (implicit savepoint, like PostgreSQL), except in a trigger and for a routine called outside a transaction by a MIRAJ-language session, where only the failing statement 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,18) (30.3): the value returned by a function (RETURNS numeric) is written, like a numeric column, without the trailing zeros of its decimal part.
• The texts of a routine or a trigger (parameters, variables, literals) are in the "C" collation, as elsewhere in the PostgreSQL language (30.3): their comparisons are case-sensitive.
• 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.

30.14 ODBC driver and Python library#

The MIRAJ ODBC Driver (chapter 29) and the Python miraj library (27.12) also connect to a PostgreSQL instance, without any API change, through the same PostgreSQL client as the tools (30.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.