Postgres: How to get the next item in an enumeration set?

Question

Consider the code below. The enum_buysell type includes only 2 values: buy and sell . I just need to get the opposite value in some cases, but the code looks ugly, imho. Is there any way to optimize it? I thought I did not use an enumeration at all, for example. make it logical, but this idea is not ideal, because it itself makes the data 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 is no function to get the next enumeration value in PostgreSQL, you have to 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 as follows:

 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 will look like this:

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