Hi, I'm new to Retool.
I have a DB (Retool Database) with a column "Status".
Status has a field type of "Enum" with a number of options.
In my GUI, I have a table view pulling data from this DB data and I've set the Status column to be editable and I want the users to be able to select one of the pre-defined enum options from a drop down.
I have set the Status column type to be a Tag (Dropdown).
I can't figure out how to get the enum values defined in the DB to populate the Option List.
Is there a way to do this?
Thanks,
-- Andrew
Hello,
You can run this query below using your Retool DB resource.
Quick note, "severity" is my enum column, so replace with your column name since the enum type will be dynamic - see second column in image. From here it should be easy to populate in the correct format for the options property
SELECT
enum.enumsortorder,
type.typname,
enum.enumlabel AS value
FROM pg_enum AS enum
JOIN pg_type AS type
ON (type.oid = enum.enumtypid)
WHERE type.typname like 'severity%'
GROUP BY enum.enumsortorder,
enum.enumlabel,
type.typname
ORDER BY enumsortorder
source: https://sadique.io/blog/2019/05/09/looking-up-enum-types-and-values-in-postgres/
Awesome! Thanks so much! Worked like a charm.
Just for posterity so others can find the solution.
- create Retool DB table with Columns that have Enum values
- Create a query like the above, for debugging purposes, I made a general query for all enum labels to ensure I can easily look for the type names I want
SELECT
type.typname,
enum.enumlabel AS value
FROM pg_enum AS enum
JOIN pg_type AS type
ON (type.oid = enum.enumtypid)
GROUP BY enum.enumsortorder,
enum.enumlabel,
type.typname
ORDER BY enumsortorder
- Create another specific query to get just the status like:
SELECT
type.typname,
enum.enumlabel AS value
FROM pg_enum AS enum
JOIN pg_type AS type
ON (type.oid = enum.enumtypid)
WHERE type.typname like 'Status%'
GROUP BY enum.enumsortorder,
enum.enumlabel,
type.typname
ORDER BY enumsortorder
*Then on the Table GUI element, make the Status column editable, set it to be a Tag (Dropdown) type and select the query for those labels.
1 Like