Postgres: How to get next item in an enum set?

Question

Consider using the code below. Type enum_buysell includes only 2 values: buy and sell. I need to just get the opposite value in some cases, but the code looks ugly, imho. Is there a way to optimise it? I thought of not using enum at all, e.g. make it boolean instead, but this idea's not perfect as it makes the data itself less obvious.

select
  datetime,
  case
    when account_id_active  = p_account_id and direction = 'buy'  then 'buy'::enum_buysell
    when account_id_active  = p_account_id and direction = 'sell' then 'sell'::enum_buysell
    when account_id_passive = p_account_id and direction = 'buy'  then 'sell'::enum_buysell
    when account_id_passive = p_account_id and direction = 'sell' then 'buy'::enum_buysell
  end as direction,
  price,
  volume
from
  deals
where
  account_id_active  = p_account_id or
  account_id_passive = p_account_id
order by
  datetime desc
limit
  10;

Answer 1

Since there's no function for getting next value of enum in PostgreSQL, you should define it yourself.

create function next_buysell (e enum_buysell)
returns enum_buysell
as $$
begin
  return (case when e='buy'::enum_buysell then 'sell'::enum_buysell
               else 'buy'::enum_buysell
          end);
end
$$ language plpgsql;

Now, you can use it like this:

postgres=# select next_buysell('sell'::enum_buysell);
 next_buysell
--------------
 buy
(1 row)

postgres=# select next_buysell('buy'::enum_buysell);
 next_buysell
--------------
 sell
(1 row)

And your CASE statement becomes:

case
  when account_id_active  = p_account_id then direction
  when account_id_passive = p_account_id then next_buysell(direction)
end as direction