Using Custom Types in Postgres

Using Types For Fun and Profit

In my last post about pulling documents from queries I showed an interesting way to return a result set using row_to_json to crunch down 1-many records into a JSON array. This works pretty well and is really fast - but it's not exactly pretty:

create or replace function get_member(member_id bigint)
returns table (
  id bigint,
  email varchar(255),
  first varchar(25),
  last varchar(25),
  last_signin_at  timestamptz,
  notes json,
  logs json,
  roles json
)
as $$

DECLARE

  found_user members;
  parsed_logs json;
  parsed_roles json;
  parsed_notes json;

BEGIN
  select * from members where members.id = member_id into found_user;

  select json_agg(x) into parsed_logs from
  (select * from logs where logs.member_id=found_user.id) x;

  select json_agg(y) into parsed_notes from
  (select * from notes where notes.member_id=found_user.id) y;

  select json_agg(z) into parsed_roles from
  (select * from roles
  inner join members_roles on roles.id = members_roles.role_id
  where members_roles.member_id=found_user.id) z;

  return query
  select found_user.id, found_user.email, found_user.first, found_user.last, found_user.last_signin_at,
  parsed_notes, parsed_logs, parsed_roles

END;

$$ LANGUAGE PLPGSQL;

Beauty is in the eye of the beholder I suppose - this looks nice to me, but one thing stands out: I don't like the anonymous table return style. I think I'll probably want to use that again somewhere so let's set that up.

The first thing to do is resolve it to a type:

create type member_summary as (
  id bigint,
  email varchar(255),
  first varchar(25),
  last varchar(25),
  last_signin_at  timestamptz,
  notes json,
  logs json,
  roles json
);

Lovely. This is a composite type in Postgres - you can define your own base types if you want - but that's a whole other story. This composite type will do nicely.

Now we can rewrite the function to be a bit more concise:

create or replace function get_member(member_id bigint)
returns setof member_type
as $$

DECLARE

  found_user members;
  parsed_logs json;
  parsed_roles json;
  parsed_notes json;

BEGIN
  select * from members where members.id = member_id into found_user;

  select json_agg(x) into parsed_logs from
  (select * from logs where logs.member_id=found_user.id) x;

  select json_agg(y) into parsed_notes from
  (select * from notes where notes.member_id=found_user.id) y;

  select json_agg(z) into parsed_roles from
  (select * from roles
  inner join members_roles on roles.id = members_roles.role_id
  where members_roles.member_id=found_user.id) z;

  return query
  select found_user.id, found_user.email, found_user.first, found_user.last, found_user.last_signin_at,
  parsed_notes, parsed_logs, parsed_roles

END;

$$ LANGUAGE PLPGSQL;

Much better. You'll notice that instead of saying returns TABLE I now need to say it's a setof a type. A "type" in Postgres can be a base type (like int, varchar, etc) or a table - which is a composite type by itself. members is a type. If you want to create your own for reusability - you sure can!

Now we can reuse this type if we like - say by finding a member by email:

create or replace function get_member_by_email(member_email varchar(255))
returns setof member_type
as $$

DECLARE
  found_id bigint;
BEGIN
  select id from members into found_id where members.email = member_email;
  return query
  select * from get_member(found_id);

END;

$$ LANGUAGE PLPGSQL;

Enums

I also have a logging table that keeps track of things in the system. For that, I like to know what type of log is being stored. If I was being strict, I'd have two tables, like this:

create table log_types(
  id serial primary key not null,
  description varchar(25)
);
create table logs(
        id serial primary key not null,
        subject_id int not null references log_types(id),
        member_id bigint not null references members(id) on delete cascade,
        entry text not null,
        data json,
        created_at timestamptz default current_timestamp
);

This works fine and there's a nice Foreign Key constraint in there to be sure I have some type of description. However there's a simpler way that, to me, is a bit more descriptive:

create type log_type as ENUM(
  'registration', 'authentication', 'activity', 'system'
);

create table logs(
    id serial primary key not null,
    subject_id log_type not null,
    member_id bigint not null references members(id) on delete cascade,
    entry text not null,
    data json,
    created_at timestamptz default current_timestamp
);

insert into logs (subject, member_id, entry)
values ('registration',11111,'Member registered');

This works basically the same way, but instead of having a simple integer in my logs table, I have the description itself with a constraint on it that it must contain one of the specified values.

Lovely. There's a lot more we can do here on the write-side of working with data. I'll cover that in the next post.