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": "..."}'
);