Functions

CRUD Operations

Functions for creating, reading, updating, and deleting documents.

Write procedures

insert, update, and delete are also exposed as PROCEDUREs. delete has a _txn_proc form but no _bulk form.

Collection and Database Management

Functions for managing collections, views, databases, and sharding.

Index Management

Building an index from SQL

createIndexes is a two-call protocol. The gateway drives both halves for you; a direct SQL caller has to do it explicitly. Create the collection first — if create_indexes_background has to create it, the build runs inline instead and step 2 does not apply.

Step 1 — queue the build, then commit:

SELECT ok, requests FROM documentdb_api.create_indexes_background(
    'mydb',
    '{ "createIndexes": "users", "indexes": [ { "key": { "email": 1 }, "name": "email_1" } ] }'::documentdb_core.bson
);
COMMIT;

 ok |                                     requests
----+----------------------------------------------------------------------------------
 t  | { "indexRequest" : { "cmdType" : "C", "ids" : [ { "$numberInt" : "42009" } ] } }

The commit is not optional. The request is queued by inserting into an ordinary table inside your transaction, and the worker that drains the queue runs in a different backend — until you commit, it cannot see the row, so the build never starts. Clients that do not autocommit (psycopg, JDBC) must commit explicitly here.

The index is not usable yet. Step 2 — pass that whole requests document to check_build_index_status, committing between attempts, and stop on ok:

SELECT ok, complete, retval FROM documentdb_api_internal.check_build_index_status(
    '{ "indexRequest" : { "cmdType" : "C", "ids" : [ { "$numberInt" : "42009" } ] } }'::documentdb_core.bson
);

Check ok before complete. A failed build also reports complete = true (with ok = false and the reason in retval), and so does a request that has simply left the queue — the implementation treats "not in the queue" as success, which is why dropping an index deliberately leaves its queue row behind. A loop that exits on complete alone will report success for a build that deadlocked. Poll until ok is false (fail) or complete is true with ok still true (success), and confirm with listIndexes if it matters.

Committing between attempts matters for the same reason it does in step 1, and additionally because holding a transaction open blocks the CREATE INDEX CONCURRENTLY the worker is trying to run. Upstream's test suite wraps all of this in a polling procedure, documentdb_test_helpers.create_indexes_background, which is worth reading as a reference implementation.

Two more behaviors are easy to miss:

• A malformed index spec does not raise a SQL error. create_indexes_background returns ok = false with the reason in retval and an empty requests, so a script that only watches for exceptions will report success on a rejected build. (A NULL spec or a read-only server does raise.)
• Something has to drain the queue. By default that is a pg_cron job running documentdb_api_internal.build_index_concurrently, scheduled by documentdb_api_internal.schedule_background_index_build_jobs; setting documentdb.indexBuildsScheduledOnBgWorker moves the work to a background worker instead. If neither is running — for example when cron.use_background_workers is off and pg_cron cannot authenticate against a hardened pg_hba.conf — queued builds never start, and anything polling for completion (including the gateway) waits indefinitely.

Diagnostic and Administration Commands

Functions backing diagnostic and administrative wire protocol commands.

User Management

Functions for creating, updating, and managing database users. Backed by the wire protocol createUser, dropUser, updateUser, and usersInfo commands.

Role Management

Functions for custom roles. Backed by the wire protocol createRole, dropRole, rolesInfo, and updateRole commands.

create_role, drop_role, and roles_info are gated behind documentdb.enableRoleCrud, which is off by default, so on a stock build they raise "The CreateRole command is currently unsupported." and its equivalents before doing any work. With it on, create_role creates the role, grants its parent roles, and records it in the documentdb_api_catalog.roles table. A non-empty privileges array fails with "Privileges on a collection are currently unsupported.", so a custom role can only inherit built-in roles. While documentdb.enableRolesAdminDBCheck is on (the default), the role functions require "$db": "admin" in the spec, from SQL as well as over the wire: they fail with "The required $db property is missing." without it and reject any other database. With the check off, neither is enforced.

Utility Functions

Example

To call any of these directly from psql:

SELECT * FROM documentdb_api.insert(
    'mydb',
    '{ "insert": "users", "documents": [ { "name": "alice" } ] }'::documentdb_core.bson
);

Because insert declares two OUT parameters, SELECT * returns them as the named columns p_result and p_success. Dropping the * would collapse them into a single composite value.

For more complete examples (cursors, aggregation, sharding) see the API Reference.