Contents

SQL Function Reference

All pg_mentat functions live in the mentat schema. After CREATE EXTENSION pg_mentat, they are accessible as mentat.function_name().

Function Naming Convention

pg_mentat provides two sets of function names:

Convenience aliases (recommended for everyday use in the default mentat schema):

SELECT mentat.t('[{:person/name "Alice"}]');
SELECT mentat.q('[:find ?e :where [?e :person/name "Alice"]]');
SELECT mentat.pull('[*]', 10001);

Core functions use the cross-backend edn_* names shared with the SQLite and DuckDB extensions (since 1.9.0):

SELECT edn_t('[{:person/name "Alice"}]');
SELECT edn_q('[:find ?e :where [?e :person/name "Alice"]]', '{}');
SELECT edn_pull('[*]', 10001);
SELECT edn_eval('(+ 1 2)');   -- only in builds with the `script` feature

Deprecated names. Before 1.9.0 these were mentat_transact, mentat_query, mentat_pull and mentat_eval. The old names still work: they are thin SQL wrappers over the edn_* functions (COMMENTed as deprecated) and will be removed in a future major release. Other functions keep their mentat_ prefix.

Quick Reference

Convenience alias Full function Description
mentat.t(edn) edn_t(edn) Transact EDN data
mentat.q(query, inputs) edn_q(query, inputs) Run a Datalog query
– edn_q_rows(query, inputs) Run a query, one JSONB row per result
mentat.pull(pattern, eid) edn_pull(pattern, eid) Pull entity attributes
mentat.pull_many(pattern, eids) mentat_pull_many(pattern, eids) Pull multiple entities
mentat.entity(eid) mentat_entity(eid) All attributes as JSON
mentat.schema() mentat_schema() Current schema
mentat.explain(query) mentat_explain(query) Show generated SQL
mentat.stats() mentat_query_stats() Execution statistics
mentat.storage() mentat_storage_stats() Storage statistics
mentat.cache_stats() mentat_stmt_cache_stats() Statement cache info
mentat.cache_clear() mentat_stmt_cache_clear() Clear statement cache

Transaction Functions

mentat.t(edn) / edn_t(edn)

Execute a transaction. Returns a JSON transaction report with tx_id, tx_instant, and tempids.

SELECT mentat.t('[
  {:db/id "tempid-1"
   :person/name "Alice"
   :person/age 30}
]');

The t alias transacts against the default store. Use the full function with a store argument for named stores:

SELECT mentat.mentat_transact_store('analytics', '[{:event/type "click"}]');

mentat_with(edn) — Speculative Transaction

Execute a transaction without persisting it. Returns the same report format, but writes nothing. Useful for validation or “what-if” analysis.

SELECT mentat.mentat_with('[
  {:person/name "Test" :person/age 99}
]');

Query Functions

mentat.q(query, inputs) / edn_q(query, inputs)

Execute a Datalog query. Returns JSONB with columns and results.

-- Simple query
SELECT mentat.q('
  [:find ?name ?age
   :where [?e :person/name ?name]
          [?e :person/age ?age]
          [(> ?age 21)]]
');

-- With input bindings (positional, matching :in clause order)
SELECT mentat.q('
  [:find ?name
   :in $ ?min-age
   :where [?e :person/name ?name]
          [?e :person/age ?age]
          [(>= ?age ?min-age)]]
', '[25]');

The inputs parameter is a JSON value: - Simple array for positional bindings: '[25]' - Empty for no inputs: '{}' or '[]'

edn_q_rows(query, inputs DEFAULT '{}') – streamed rows

RETURNS SETOF JSONB: one JSON array per result row, in :find order, with the same value encoding and the same inputs (:in bindings, asOf / since / history, limit / offset) as edn_q. Use it when the result is large – edn_q returns one JSONB value, which PostgreSQL caps at 1 GB and which must be built in memory before the first row reaches the client – or when you want to join / filter / aggregate the rows in SQL:

SELECT r->>0 AS name, (r->>1)::int AS age
FROM edn_q_rows('[:find ?n ?a :where [?e :person/name ?n] [?e :person/age ?a]]') r
WHERE (r->>1)::int > 21;

Rows are read from a cursor in batches of 1000. mentat.max_result_rows applies exactly as for edn_q (more rows than the limit is an error, not a silent truncation), so both functions give the same answer for the same settings; SET LOCAL mentat.max_result_rows = 0 for an unbounded stream. Default store only (like edn_q).

mentat_tune_indexes(dry_run DEFAULT true) – automatic indexes

Report (dry run) or apply the automatic index manager’s creates / drops: RETURNS TABLE(action, index_name, table_name, reason). See Operations: automatic index management.

mentat.explain(query) / mentat_explain(query)

Show the generated SQL and PostgreSQL’s EXPLAIN output without executing the query.

SELECT mentat.explain('[:find ?name :where [?e :person/name ?name]]');

mentat_query_sql(query) — Generated SQL

Return only the generated SQL string (no execution, no EXPLAIN).

SELECT mentat.mentat_query_sql('[:find ?name :where [?e :person/name ?name]]');

mentat_query_view(name, query) — Create SQL VIEW from Datalog

Create a PostgreSQL VIEW backed by a Datalog query:

SELECT mentat.mentat_query_view('people_over_30', '
  [:find ?name ?age ?email
   :where [?e :person/name ?name]
          [?e :person/age ?age]
          [?e :person/email ?email]
          [(> ?age 30)]]
');

-- Now use it like any SQL view
SELECT * FROM mentat.people_over_30 WHERE name LIKE 'A%';

Pull Functions

mentat.pull(pattern, eid) / edn_pull(pattern, eid)

Pull attributes for a single entity. Returns a nested JSON document.

-- Pull everything
SELECT mentat.pull('[*]', 10001);

-- Pull specific attributes with nested refs
SELECT mentat.pull('[
  :person/name
  :person/age
  {:person/friends [:person/name :person/age]}
]', 10001);

-- Reverse lookup: who has this entity as a friend?
SELECT mentat.pull('[:person/name :person/_friends]', 10001);

-- With modifiers
SELECT mentat.pull('[
  :person/name
  {(:person/friends :limit 5 :as :top-friends) [:person/name]}
  {(:person/_friends :as :admirers) [:person/name]}
]', 10001);

mentat.pull_many(pattern, eids) / mentat_pull_many(pattern, eids)

Pull the same pattern for multiple entities. Returns a JSON array.

SELECT mentat.pull_many('[:person/name :person/age]', ARRAY[10001, 10002, 10003]);

mentat.entity(eid) / mentat_entity(eid)

Return all current attributes for an entity as a flat JSON map (equivalent to pull('[*]', eid)).

SELECT mentat.entity(10001);

Schema Functions

mentat.schema() / mentat_schema()

Return the full schema as JSON, keyed by attribute ident.

SELECT mentat.schema();

Store Management

mentat.create_store(name, description)

Create a new isolated store with its own schema, tables, and indexes.

SELECT mentat.create_store('analytics', 'Event tracking store');

mentat.drop_store(name)

Drop a store and all its data (irreversible).

mentat.list_stores()

List all stores with metadata.

mentat.rename_store(old_name, new_name)

Rename an existing store.

Time Travel Functions

mentat.log(store, from_tx, to_tx)

Return the transaction log for a range of transactions.

SELECT mentat.log('default', 1000001, 1000010);

mentat.diff(store, from_tx, to_tx)

Compute the diff between two points in time — what was added and retracted.

SELECT mentat.diff('default', 1000003, 1000007);

Time-travel via query parameters

Pass as_of_tx or since_tx to query functions:

-- Query the database as of transaction 1000005
SELECT mentat.q('
  [:find ?name :where [?e :person/name ?name]]
', '[]', 1000005, NULL);

Excision Functions

mentat_excise(store, entity_id, attribute)

Permanently remove datoms from the database, including all history. This is the only operation that truly deletes data (GDPR compliance).

-- Remove all data for an entity
SELECT mentat.mentat_excise('default', 10042, NULL);

-- Remove only a specific attribute
SELECT mentat.mentat_excise('default', 10042, ':person/email');

Subscription Functions

mentat.subscribe(store, name, query)

Subscribe to changes matching a Datalog query pattern. Uses PostgreSQL LISTEN/NOTIFY.

SELECT mentat.subscribe('default', 'new_people',
  '[:find ?e :where [?e :person/name]]');

-- In another session:
LISTEN mentat_subscription_new_people;

mentat.unsubscribe(store, name)

Remove a subscription.

Materialized View Functions

mentat.materialize(store, name, query)

Create a materialized view from a Datalog query for faster repeated access.

SELECT mentat.materialize('default', 'active_users',
  '[:find ?e ?name :where [?e :person/name ?name] [?e :person/active true]]');

mentat.refresh(store, name)

Refresh a materialized view with current data.

Statistics & Monitoring

mentat.stats() / mentat_query_stats()

Query execution statistics: call counts, timing, cache hit rates.

mentat.storage() / mentat_storage_stats()

Storage statistics: row counts, table sizes, index sizes.

mentat.cache_stats() / mentat_stmt_cache_stats()

Prepared statement cache statistics.

mentat.cache_clear() / mentat_stmt_cache_clear()

Clear the statement cache.

mentat_health_check()

Extension health check (returns JSON with status, version, store count).

mentat_slow_queries(threshold_ms)

Return recently logged slow queries exceeding the given threshold.

EDN Helper Functions

These operate on EDN-formatted text values and are installed in the public schema for convenience.

Function Description
edn_get(edn, key) Extract a value from an EDN map
edn_nth(edn, index) Extract Nth element from an EDN vector
edn_count(edn) Count elements in an EDN collection
edn_keys(edn) Keys of an EDN map as a vector
edn_values(edn) Values of an EDN map as a vector
edn_contains(edn, key) Check if a map contains a key
edn_type(edn) Type of an EDN value
edn_pretty(edn, width) Pretty-print EDN with indentation

Bootstrap Functions

mentat.bootstrap_schema()

Re-run the bootstrap schema installation. Called automatically during CREATE EXTENSION but can be invoked to repair a corrupted schema.