Functions

CRUD Operations

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

Write procedures

insert and update are also exposed as PROCEDUREs. delete has no procedure form in v0.114-0.

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

All four role functions were added in v0.106-0, together with wire-protocol support for createRole. Support for the dropRole and rolesInfo commands followed in v0.108-0. updateRole is routed by the gateway to documentdb_api.update_role.

All of them are gated behind documentdb.enableRoleCrud, added in v0.108-0 and off by default, so on a stock build create_role, drop_role, and roles_info raise "The CreateRole command is currently unsupported." and its equivalents before doing any work. The wire-protocol commands are additionally required to run against the admin database (documentdb.enableRolesAdminDBCheck, on by default since v0.109-0); the user management functions above are not.

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.