Using Posgres as an Indexed Document Store

create_documentstore.sql

CREATE TABLE document_store (key CHAR(100) PRIMARY KEY, bucket CHAR(100), value JSON);

documentstore_create_index.sql

CREATE INDEX documentstore_value_lastname_index on document_store ((value->>'lastname'));

documentstore_create_unique_index.sql

CREATE UNIQUE INDEX documentstore_value_username_index on document_store ((value->>'username'));

documentstore_get_by_field.sql

CREATE FUNCTION documentstore_get_by_field(field TEXT, bucket CHAR(100), value TEXT, limit INTEGER)
RETURNS TABLE(key TEXT, value TEXT) AS $$BODY$$
BEGIN
    RETURN query
    EXECUTE 'SELECT key::text, value::text FROM document_store '
    || 'WHERE bucket = ' || quote_literal(bucket)
    || ' AND value->>(' || quote_literal(field) || ') = ' || quote_literal(value)
    || ' LIMIT ' || quote_literal(limit);
END;
$$
LANGUAGE plpgsql;

documentstore_get_by_key.sql

CREATE FUNCTION documentstore_get_by_key(key CHAR(100), bucket CHAR(100))
RETURNS TABLE(key TEXT, value TEXT) AS $$BODY$$
BEGIN
    RETURN query
    EXECUTE 'SELECT key::text, value::text FROM document_store '
    || 'WHERE key = ' || quote_literal(key);
    || ' AND bucket = ' || quote_literal(bucket);
END;
$$
LANGUAGE plpgsql;

documentstore_key_range.sql

CREATE FUNCTION documentstore_key_range(bucket CHAR(100), low CHAR(100), high CHAR(100), limit INTEGER)
RETURNS TABLE(key TEXT, value TEXT) AS $$BODY$$
BEGIN
    RETURN query
    EXECUTE 'SELECT key::text, value::text FROM document_store '
    || 'WHERE bucket = ' || quote_literal(bucket)
    || ' AND key >= ' || quote_literal(low)
    || ' AND key <= ' || quote_literal(high)
    || ' LIMIT ' || quote_literal(limit);
END;
$$
LANGUAGE plpgsql;

documentstore_put.sql

CREATE OR REPLACE FUNCTION documentstore_put(tname TEXT, bucket TEXT, key TEXT, value TEXT)
RETURNS VOID AS $$
BEGIN
    LOOP
        -- first try to update the key
        EXECUTE 'UPDATE document_store'
        || ' SET value = '
        || quote_nullable(value)
        || ' WHERE key = '
        || quote_literal(key)
        || ' AND bucket = '
        || quote_literal(bucket);
        IF found THEN
            RETURN;
        END IF;
        -- not there, so try to insert the key
        -- if someone else inserts the same key concurrently,
        -- we could get a unique-key failure
        BEGIN
            EXECUTE 'INSERT INTO document_store (value, key, bucket) values ( '
            || quote_nullable(value) || ', '
            || quote_literal(key) || ', '
            || quote_literal(bucket) || ')';
            RETURN;
        EXCEPTION WHEN unique_violation THEN
            -- Do nothing, and loop to try the UPDATE again.
            RETURN;
        END;
    END LOOP;
END;
$$
LANGUAGE plpgsql;

documentstore_range_by_field.sql

CREATE FUNCTION documentstore_range_by_field(field TEXT, bucket CHAR(100), low TEXT, high TEXT, limit INTEGER)
RETURNS TABLE(key TEXT, value TEXT) AS $$BODY$$
BEGIN
    RETURN query
    EXECUTE 'SELECT key::text, value::text FROM document_store '
    || 'WHERE bucket = ' || quote_literal(bucket)
    || ' AND value->>(' || quote_literal(field) || ') >= ' || quote_literal(low)
    || ' AND value->>(' || quote_literal(field) || ') <= ' || quote_literal(high)
    || ' LIMIT ' || quote_literal(limit);
END;
$$
LANGUAGE plpgsql;