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.
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
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
For a much shorter query to get the ENUM options, do as @Andrew_Hogue suggests to get the name of the enum you want. Then, use this query:
SELECT unnest(enum_range(NULL::milestone_enum_085ec9cc)) AS milestones;
In this query, milestone_enum_085ec9cc is the name of the enum in my database, and milestones is the "variable" that will receive an array of the enum options.
