Get enum type from database

[pintariching]

Environment

Is it possible to query the database for an enum type?
I have a table that uses them and I would like, if possible, to get the options from the database to display them in a form?

[dwagin]

SELECT unnest(enum_range(NULL::myenum))https://www.postgresql.org/docs/current/functions-enum.html

[pintariching]

So I guess I have to make functions query that? I hoped there was a built in way to get them out with postgrest like there is with views and functions.

[dwagin]

Just create a view for this. Closed #2105.

[rzetterberg]

@pintariching

I hoped there was a built in way to get them out with postgrest like there is with views and functions.

There is! Since PostgREST exposes a swagger definition (if unfamiliar, it's a JSON document that describes every available API endpoint, what parameters they accept, etc.) for the whole API at the base URL.

For example, I have the following enum and table:

CREATE TYPE access.account_kind
    AS ENUM ( 'owner'
            , 'customer_admin'
            , 'customer_user'
            );

CREATE TABLE access.accounts (
  pk            uuid                PRIMARY KEY DEFAULT gen_random_uuid(),
  name          text                NOT NULL,
  kind          access.account_kind NOT NULL,
  password_hash text                NOT NULL CHECK (length(password_hash) = 60),
  created_at    timestamp           NOT NULL DEFAULT now(),
  updated_at    timestamp           NOT NULL DEFAULT now(),

  UNIQUE(name)
);

This table is then exposed to the API as the view api.accounts.

If I load the root URL of my API and check the swagger definition, it contains a member called definitions where I can find my table and the list of values for my enum:

{
    "accounts": {
      "properties": {
        "pk": {
          "format": "uuid",
          "type": "string",
          "description": "Note:\nThis is a Primary Key.<pk/>"
        },
        "name": {
          "format": "text",
          "type": "string"
        },
        "kind": {
          "format": "access.account_kind",
          "type": "string",
          "enum": [
            "owner",
            "customer_admin",
            "customer_user"
          ]
        },
        "created_at": {
          "format": "timestamp without time zone",
          "type": "string"
        },
        "updated_at": {
          "format": "timestamp without time zone",
          "type": "string"
        }
      },
      "type": "object"
    },
    ... rest of my table definitions
  }