Check if enum type exists and add value

Viewed 239

I'm new to PostgreSQl. I'm trying to add new values to type if type exists:

ALTER type ProcessStatus ADD VALUE IF NOT EXISTS 'Processing';

Above query will add 'Processing' to type ProcessStatus, but I want to check if type exists first.

I tried like below

DO $$                                               
BEGIN                                                        
  IF EXISTS(SELECT oid FROM pg_type WHERE typname = 'ProcessStatus')
  THEN                                                        
      ALTER TYPE ProcessStatus ADD VALUE IF NOT EXISTS 'Processing';
  END IF;                                                        
END $$;

I get this error when type doesn't exist:

SQL Error [25001]: ERROR: ALTER TYPE ... ADD cannot be executed from a function

0 Answers
Related