postgresql – Postgres:如何获取枚举集中的下一项?
发布时间:2020-12-13 18:10:49 所属栏目:百科 来源:网络整理
导读:考虑使用下面的代码.类型enum_buysell仅包含2个值:买入和卖出.在某些情况下我需要得到相反的值,但代码看起来很难看,imho.有没有办法优化它?我想过根本不使用enum,例如把它改为布尔值,但这个想法并不完美,因为它使数据本身不那么明显. select datetime,case
考虑使用下面的代码.类型enum_buysell仅包含2个值:买入和卖出.在某些情况下我需要得到相反的值,但代码看起来很难看,imho.有没有办法优化它?我想过根本不使用enum,例如把它改为布尔值,但这个想法并不完美,因为它使数据本身不那么明显.
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;
由于在PostgreSQL中没有获取枚举的下一个值的功能,您应该自己定义它.
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; 现在,您可以像这样使用它: postgres=# select next_buysell('sell'::enum_buysell); next_buysell -------------- buy (1 row) postgres=# select next_buysell('buy'::enum_buysell); next_buysell -------------- sell (1 row) 并且您的CASE声明变为: case when account_id_active = p_account_id then direction when account_id_passive = p_account_id then next_buysell(direction) end as direction (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |