Useful key/value functions for PostgreSQL

keyvalue.sql

create or replace function set_keyval(tablename varchar, keyname varchar, valuestring text, schemaname varchar default 'public') returns table(key varchar, value text, created_at timestamp, updated_at timestamp) as $$
begin
  return query execute 'update '||quote_ident(schemaname)||'.'||quote_ident(tablename)||' set value = $2, updated_at = now() where key = $1 returning *' using keyname, valuestring;
  if not found then
    return query execute 'insert into '||quote_ident(schemaname)||'.'||quote_ident(tablename)||' (key, value, created_at, updated_at) values ($1, $2, now(), now()) returning *' using keyname, valuestring;
  end if;
end;
$$ language 'plpgsql';

create or replace function get_keyval(tablename varchar, keyname varchar, schemaname varchar default 'public') returns table(key varchar, value text, created_at timestamp, updated_at timestamp) as $$
begin
  return query execute 'select * from '||quote_ident(schemaname)||'.'||quote_ident(tablename)||' where key = $1' using keyname;
end;
$$ language 'plpgsql';

create or replace function del_keyval(tablename varchar, keyname varchar, schemaname varchar default 'public') returns table(key varchar, value text, created_at timestamp, updated_at timestamp) as $$
begin
  return query execute 'delete from '||quote_ident(schemaname)||'.'||quote_ident(tablename)||' where key = $1 returning *' using keyname;
end;
$$ language 'plpgsql';

-- create a keyvalue table
create unlogged table sessions(
  key varchar primary key,
  value text,
  created_at timestamp,
  updated_at timestamp
);

show-example.sql

-- set a keyvalue on the sessions table
select * from set_keyvalue('sessions', 'abc123', 'hello world');

-- set a keyvalue on the private.items table
select * from set_keyvalue('items', 'def456', 'howdy partner', 'private');

-- get a keyvalue from the sessions table
select * from get_keyvalue('sessions', 'abc123');

-- delete a keyvalue from the private.items table
select * from del_keyvalue('items', 'def456', 'private');