Dynamically create Postgresql queries

We can use the format and execute functions for this.

Create your sql string with format.

format($__$
  create table %I (id serial primary key, name %I not null);
$__$, 'cats', 'name');

%1$I allows you to specify the position of the argument. If I wanted to use an sql literal I'd use %L or %s for a string.

Then put it through execute(sql_string);. In full:

do $$
declare
  sql_str text;
begin
  sql_str := format($__$
    create table %I (id serial primary key, %I text not null);
  $__$, 'cats', 'name');
  execute(sql_str);
end
$$;
postgresql

Postgresql format string

We use the format(string, values ...) function.

The placeholders are %s for a string, %I for an sql identifier and %L for an sql literal.

If you want to refer to a position you can, for example, refer to the second with %2$s with the s referring to the above.

format('hello %s %1$s %s', 'again', 'indeed');

gives you hello again again indeed.

postgresql

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

CRUD functions for storing JSON in postgresql

Let's create a table with an id and a json column. We use jsonb because postgresql offers us more functions to manipulate and use it.

create table jsonthing (
  id serial primary key,
  json jsonb not null
);

Here are the functions

create function jsonthing_select()
returns setof jsonthing
language plpgsql
as $$
begin
  return query select * from jsonthing;
end
$$;

create function jsonthing_insert(
  p_json jsonb)
returns setof jsonthing
language plpgsql
as $$
begin
  insert into jsonthing (json)
    values (p_json);
  return query select * from jsonthing;
end
$$;

create function jsonthing_update(
  p_id int,
  p_json jsonb)
returns setof jsonthing
language plpgsql
as $$
begin
  update jsonthing 
    set json = json || p_json
    where jsonthing.id = p_id;
  return query select * from jsonthing;
end
$$;

create function jsonthing_delete(
  p_id)
returns setof jsonthing
language plpgsql
as $$
begin
  delete from jsonthing
    where jsonthing.id = p_id;
  return query select * from jsonthing;
end
$$;

Note our jsonthing_update function merges the new json object with the existing one. This means it won't delete fields. Another function could replace the json object with little effort.

We use it like this below.

select * from jsonthing_insert(
  '{"akey":"avalue"}'
);

select * from jsonthing_insert(
  '{ "anotherkey":"anothervalue"}'
);

select * from jsonthing_update(1,
  '{ "additionalkey":"addtionalvalue"}'
);

select * from jsonthing_delete(1);

select * from jsonthing_select();
postgresql

WIP: Postgresql simple auth, part 2: CRUD

This is just for fun.

We can create our select, insert, update, and delete functions (or create, update and delete) now we have our auth functions.

Let's create a thing table with a auth username reference. This means a thing can only be owned by one user.

create table thing(
  id serial primary key,
  username text reference auth(username) not null,
  text text not null
);

We use our auth_session() function for the authorization and pass in session token to each function. We throw an exception from the functions through auth_session() if the session token is bad. We return the whole table for that user on return of each function.

create function thing_select(
  p_session text)
returns setof thing
language plpgsql
as $$
declare
  p_username text := auth_session(p_session);
begin
  return query select thing.* from thing
    where thing.username = p_username;
end
$$;

create function thing_insert(
  p_session text,
  p_text text)
returns setof thing
language plpgsql
as $$
declare
  p_username text := auth_session(p_session);
  p_id int;
begin
  insert into thing (username, text)
    values (p_username, p_text);
  return query select * 
    from thing_select(p_session);
end
$$;

create function thing_update(
  p_session text,
  p_id int,
  p_text text)
returns setof thing
language plpgsql
as $$
declare
  p_username text := auth_session(p_session);
begin
  update thing 
    set text = p_text
    where thing.id = p_id
    and thing.username = p_username;
  return query select * 
    from thing_select(p_session);
end
$$;

create function thing_delete(
  p_session text,
  p_id int)
returns setof thing
language plpgsql
as $$
declare
  p_username text := auth_session(p_session);
begin
  delete from thing
    where thing.id = p_id
    and thing.username = p_username;
  return query select * 
    from thing_select(p_session);
end
$$;

Those functions don't say when the user tries to update or delete a row that doesn't belong to them. It silently fails. This could be improved with little effort.

You can use this, with a session token, as follows.

select * from thing_select(
  '...'
);
select * from thing_insert(
  '...',
  'first thing'
);
select * from thing_update(
  '...',
  1,
  'updated thing'
);
select * from thing_delete(
  '...',
  1
);
postgresql

Page 3 of 99
prev next
Click me