API Docs

This document is auto-generated from the core bedquilt source code, and describes the low-level SQL API of BedquiltDB.


collection_constraints

bq_add_constraints

  • params: i_coll text, i_jdoc jsonb
  • returns: boolean
  • language: plpgsql
Add a set of constraints to the collection.
The supplied json document should be in the form {field: constraint_spec},
for example:
  {"age": {"$required": true,
           "$notnull": true,
           "$type": "number"}}
Valid constraints are: $required, $notnull and $type.
- {$required: 1} : the field must be present in all documents
- {$notnull: 1} : if the field is present, its value must not be null
- {$type: '<type>'} : if the field is present and has a non-null value,
      then the type of that value must match the specified type.
      Valid types are "string", "number", "object", "array", "boolean".
Returns a boolean indicating whether any of the constraints newly applied.
Example:
  select bq_add_constraints('things', '{"name": {"$required": true}}')

bq_remove_constraints

  • params: i_coll text, i_jdoc jsonb
  • returns: boolean
  • language: plpgsql
Remove constraints from collection.
The supplied json document should match the spec for existing constraints.
Returns True if any of the constraints were removed, False otherwise.
Example:
  select bq_remove_constraints('things', '{"name": {"$required": true}}')

bq_list_constraints

  • params: i_coll text
  • returns: setof text
  • language: plpgsql
Get a list of text descriptions of constraints on this collection.
Example:
  select bq_list_constraints('orders')

collection_ops

bq_create_collection

  • params: i_coll text
  • returns: BOOLEAN
  • language: plpgsql
Create a collection with the specified name.
Example:
  select bq_create_collection('orders');

bq_list_collections

  • params: None
  • returns: table(collection_name text)
  • language: plpgsql
Get a list of existing collections.
This checks information_schema for tables matching the expected structure.
Example:
  select bq_list_collections();

bq_delete_collection

  • params: i_coll text
  • returns: BOOLEAN
  • language: plpgsql
Delete/drop a collection.
At the moment, this just drops whatever table matches the collection name.
Example:
  select bq_delete_collection('orders');

bq_collection_exists

  • params: None
  • returns: boolean
  • language: plpgsql
Check if a collection exists.
Example:
  select bq_collection_exists('orders');

document_reads

bq_find_one

  • params: i_coll text, i_json_query jsonb, i_skip integer DEFAULT 0, i_sort jsonb DEFAULT null
  • returns: table(bq_jdoc jsonb)
  • language: plpgsql
Find one document from a collection, matching a query document.
Effectively the same as bq_find with limit set to 1.
Params:
  - i_coll: collection name
  - i_json_query: the query document
  - i_skip: (optional) number of documents to skip, default 0
  - i_sort: (optional) json array of sort specifications, default null
Example:
  select bq_find_one('orders', '{"processed": false}');

bq_find_one_by_id

  • params: i_coll text, i_id text
  • returns: table(bq_jdoc jsonb)
  • language: plpgsql
Find a single document from a collection, by it's `_id` property.
This function is potentially faster than the equivalent call to bq_find_one
with a '{"_id": "..."}' query document.
Example:
  select bq_find_one_by_id('things', 'fa0c852e4bc5d384b5f9fde5');

bq_find

  • params: i_coll text, i_json_query jsonb, i_skip integer DEFAULT 0, i_limit integer DEFAULT null, i_sort jsonb DEFAULT null
  • returns: table(bq_jdoc jsonb)
  • language: plpgsql
Find documents from a collection, matching a query document.
Params:
  - i_coll: collection name
  - i_json_query: the query document
  - i_skip: (optional) number of documents to skip, default 0
  - i_limit: (optional) number of documents to limit the result set to,
  - i_sort: (optional) json array of sort specifications, default null
Example:
  select bq_find('orders', '{"processed": false}');
  select bq_find('orders', '{"processed": false}', 2, 10, '[{"orderTime": -1}]');

bq_count

  • params: i_coll text, i_doc jsonb
  • returns: integer
  • language: plpgsql
Count documents in a collection, matching a query document.
Example:
  select bq_countt('orders', '{"processed": true}')

bq_distinct

  • params: i_coll text, i_key_path text
  • returns: table(val jsonb)
  • language: plpgsql
Get a sequence of the distinct values present in the collection for a given key,
Example:
  select bq_distinct('people', 'address.city')

document_writes

bq_insert

  • params: i_coll text, i_jdoc jsonb
  • returns: text
  • language: plpgsql
Insert a document into a collection.
Raises an error if a document already exists with the same `_id` field.
If the document doesn't contain an `_id` field, then one will be randomly generated
Example:
  select bq_insert('things', '{"name": "wrench"}');

bq_remove

  • params: i_coll text, i_jdoc jsonb
  • returns: setof integer
  • language: plpgsql
Remove documents from a collection, matching a query document.
Returns count of deleted documents.
Example:
  select bq_remove('orders', '{"cancelled": true}');

bq_remove_one

  • params: i_coll text, i_jdoc jsonb
  • returns: setof integer
  • language: plpgsql
Remove a single document from a collection, matching a query document.
The first document to match the query will be removed.
Returns count of deleted documents, either one or zero.
Example:
  select bq_remove_one('orders', '{"cancelled": true}');

bq_remove_one_by_id

  • params: i_coll text, i_id text
  • returns: setof integer
  • language: plpgsql
Remove a single document from a collection, by its `_id` field.
Returns count of deleted documents, either one or zero.
Example:
  select bq_remove_one_by_id('orders', '4d733fb148e7d89f7c569655');

bq_remove_many_by_ids

  • params: i_coll text, i_ids jsonb
  • returns: setof integer
  • language: plpgsql
Remove many documents, by their `_id` fields.

bq_save

  • params: i_coll text, i_jdoc jsonb
  • returns: text
  • language: plpgsql
Save a document to a collection.
Similar to `bq_insert`, but will overwrite an existing document if one with a matching
`_id` field is found. Can be used to either create new documents or update existing documents.
Example:
  select bq_save('things', '{"_id": "abc", "name": "wrench"}');

utilities

bq_util_generate_id

  • params: None
  • returns: char(24)
  • language: plpgsql
Generate a random string ID.
Used by the document write functions to populate the '_id' field
if it is missing.