How to pass ENUM variable as input for POSTGRESQL functions

Question

I have a function in MySQL which works fine:

CREATE  PROCEDURE `Accounts_Active`(IN_DeptName VARCHAR(255), IN_Src ENUM('TRAINING','ELZA'))
BEGIN
END$$
DELIMITER ;

But when converted to PostgreSQL:

CREATE or replace FUNCTION Accounts_Active(IN_DeptName VARCHAR(255), IN_Src ENUM('TRAINING','ELZA'))
RETURNS void
AS
$$
BEGIN
      RAISE INFO '    ';
END;
$$ LANGUAGE plpgsql;

The following error occurs:

ERROR:  type enum does not exist
SQL state: 42704

Any guidance on how I can fix this error would be appreciated.

You don't define an enum on the fly. You need to have it already defined, then in your function you can say `in_src your_enum_type` — 404, Feb 04 '20 at 15:18
Hi there, welcome to Stack Overflow. As 404 says, you need to define your enum before you can use it. Check out the PostgreSQL docs https://www.postgresql.org/docs/9.1/datatype-enum.html — cortexlock, Feb 04 '20 at 15:19

score 6 · Accepted Answer · answered Feb 04 '20 at 15:32

Create an enum data type:

CREATE TYPE atype AS ENUM ('TRAINING', 'ELZA');

Then you can use it as function parameter:

CREATE FUNCTION Accounts_Active(
   IN_DeptName text,
   IN_Src atype
) RETURNS void
...

When using enums, remember that you can add values to such a data type, but never again remove them. Often you will be better of using a string data type like text, but of course then you have to write code that checks the input for validity.

How to pass ENUM variable as input for POSTGRESQL functions

1 Answers1