ENUM alternative?

Queries and Resources
1

I am trying to find an alternative to ENUM-queries to display my Retool database ENUMs.

Since my database contains a lot of ENUMs I simply have a lot of queries to display said ENUMs or retrieve them from database. Since my 25+ queries are not only a source of errors but also reduce app performance I was just wondering, is there another way to retrieve ENUM input, e.g. for a select menu?

Thanks for any help!

3

Hey @w7_gmbh! Thanks for reaching out.

I can see how that might become tedious and even cause some performance issues. Unfortunately, I don't think there's a better way to query the options associated with a column of type ENUM, especially in a PostgreSQL database.

Do you expect those options to change very often? If not, you might consider defining them directly in Retool. Alternatively, you can still query them from your database but cache the results so that you aren't having to execute those queries nearly as often.

4

I have these as a Shared Queries in my Retool Query Library if it helps?

Get ENUM values for table.column

SELECT
  enumlabel as enum_values
FROM
  pg_enum 
WHERE
  enumtypid = (
    SELECT
      atttypid
    FROM
      pg_attribute
    JOIN
      pg_type ON pg_type.oid = pg_attribute.atttypid
    WHERE 
      attrelid = {{ table_name }}::regclass
      AND attname = {{ column_name }}
      AND pg_type.typtype = 'e'
  );

Get ENUM label for table.column

SELECT
  pg_type.typname AS enum_type
FROM
  pg_attribute
JOIN
  pg_type ON pg_type.oid = pg_attribute.atttypid
WHERE 
  attrelid = {{ table_name }}::regclass
  AND attname = {{ column_name }}
  AND pg_type.typtype = 'e';

Another idea would be to create a View of all the enums needed, so you can query them all in one shot. Since retool has no native mechanism to do this, you will have to connect remotely to the DB.

5

Hi Darren, Thanks for reaching out! I do not expect this to change very often, it is more the fact that with a larger database I have 15-20 ENUM queries running in my software, all of which may produce errors, slow down app performance. So I was just wondering if there is any way to avoid them.

6

One option, as I mentioned previously, is to simply hard-code the options within Retool. This isn't ideal, though, even if you don't expect those values to change very often.

Inspired by @khill-fbmc, the other option is to write a single query that returns the options for all columns of type ENUM, as shown below.

image
image1000×1228 77.4 KB

Feel free to tweak the logic, but this implementation returns an object with an array of the options associated with each column.

image