WIP: pg ignore

create table XXX(
  id serial primary key,
  username text references auth(username) not null,
  json jsonb not null
);

And

create or replace function _XXX_select(
  p_json jsonb)
returns setof XXX
language plpgsql
as $$
declare
  p_username text := _auth_session(p_json->>'session');
begin
  return query select XXX.* from XXX
    where XXX.username = p_username;
end
$$;

create or replace function _XXX_insert(
  p_json jsonb)
returns setof XXX
language plpgsql
as $$
declare
  p_username text := _auth_session(p_json->>'session');
begin
  insert into XXX (username, json)
    values (p_username, p_json->'json');
  return query select * 
    from _XXX_select(p_json);
end
$$;

create or replace function _XXX_update(
  p_json jsonb)
returns setof XXX
language plpgsql
as $$
declare
  p_username text := _auth_session(p_json->>'session');
begin
  update XXX 
    set json = json || (p_json->'json')::jsonb
    where XXX.id = (p_json->>'id')::int
    and XXX.username = p_username;
  return query select * 
    from _XXX_select(p_json);
end
$$;

create or replace function _XXX_delete(
  p_json jsonb)
returns setof XXX
language plpgsql
as $$
declare
  p_username text := _auth_session(p_json->>'session');
begin
  delete from XXX
    where XXX.id = (p_json->>'id')::int
    and XXX.username = p_username;
  return query select * 
    from _XXX_select(p_json);
end
$$;

And

select * from _xxx_insert(
  '{
    "session": "...",	
	"json": {"akey1":"avalue1"}
  }'
);

select * from _xxx_select(
  '{"session":"..."}'
);


select * from _xxx_update(
  '{ "id": 1,
     "session": "...",
     "json": {
       "additionalkey":"addtionalvalue"	
     }
  }'
);

select * from _xxx_delete(
  '{"id": 4, "session": "..."}'
);

wip

Edit on github
comments powered by Disqus
Click me