Skip to content

Instantly share code, notes, and snippets.

@wess

wess/doc_store.sql

Created February 10, 2023 14:48
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save wess/23a00b8273fd98bddae8e22dc8e14cd5 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save wess/23a00b8273fd98bddae8e22dc8e14cd5 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. wess created this gist Feb 10, 2023.
    50 changes: 50 additions & 0 deletions doc_store.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,50 @@
    CREATE EXTENSION IF NOT EXISTS plpgsql;

    CREATE TABLE doc_store (
    key text PRIMARY KEY,
    value json
    );

    CREATE OR REPLACE FUNCTION insert_key_value(p_key text, p_value json)
    RETURNS void AS $$
    BEGIN
    INSERT INTO doc_store (key, value)
    VALUES (p_key, p_value);
    END;
    $$ LANGUAGE plpgsql;

    CREATE OR REPLACE FUNCTION update_key_value(p_key text, p_value json)
    RETURNS void AS $$
    BEGIN
    UPDATE doc_store
    SET value = p_value
    WHERE key = p_key;
    END;
    $$ LANGUAGE plpgsql;

    CREATE OR REPLACE FUNCTION get_value(p_key text)
    RETURNS json AS $$
    DECLARE
    result json;
    BEGIN
    SELECT value INTO result
    FROM doc_store
    WHERE key = p_key;
    RETURN result;
    END;
    $$ LANGUAGE plpgsql;

    CREATE OR REPLACE FUNCTION delete_key_value(p_key text)
    RETURNS void AS $$
    BEGIN
    DELETE FROM doc_store
    WHERE key = p_key;
    END;
    $$ LANGUAGE plpgsql;

    CREATE OR REPLACE FUNCTION key_exists(p_key text)
    RETURNS boolean AS $$
    BEGIN
    RETURN (SELECT COUNT(*) FROM doc_store WHERE key = p_key) > 0;
    END;
    $$ LANGUAGE plpgsql;