Translate states to readable names

When extending the software with custom queries or reports, it is sometimes needed to translate values in the database to readable states.

Nerd alert, not for all audiences   

The query below to get the states of automatic emails below was hard to process

What does it mean, state 2 or 4…  Even when you would make a nice mapping of these values yourself in Excel or another tool, we might change the values or add new ones.  This would mean you would no longer be able to make clear queries.

In version 6.20.05.04 you’ll be able to use new functions that resolve these values to readable text. Use the functions CS_ENUM_AS_TEXT and CS_ENUM_AS_ENUM to translate between values and text.

The biggest advantage of this approach is that by writing it once you have a readable representation and you are ready for changes in the future.  You’ll never have to touch this query again and you’ll always have a nice textual representation of the state.

To list all types available you can use the function cs_enum_types

select * from cs_enum_types()

To get all values and names of a state us cs_enum_values

select * from cs_enum_values('UnitKind')