How to cast string value to enum

Question

I have a table with an enum type in it, and I created a function to add data to that table. I want that function to be generous in what to accept, so I take a text as the enum type and want to cast it later.

This is the enum:

CREATE TYPE public.enum_log_priority AS ENUM (     'critical','error','warning','notice','debug' ); 

And this is the function:

CREATE OR REPLACE FUNCTION public.log_write(     _message text,     _priority text ) RETURNS integer AS $body$ BEGIN     _priority = lower(_priority);     INSERT INTO log (message, priority) VALUES (_message, _priority);      RETURN 0; END $body$ LANGUAGE 'plpgsql'; 

I know this doesn't work:

ERROR: column "priority" is of type enum_log_priority but expression is of type text

but how can I do this?

Answer 1

Use syntax like below during insertion

'critical'::enum_log_priority  

Please see some link as well

http://www.postgresql.org/docs/9.1/static/datatype-enum.html

Inserting into custom SQL types with prepared statements in java

java enum and postgresql enum

Answer 2

change your function like this:

CREATE OR REPLACE FUNCTION public.log_write(     _message text,     _priority text ) RETURNS integer AS $body$ BEGIN     _priority = lower(_priority);     INSERT INTO log (message, priority) VALUES (_message, _priority::enum_log_priority);      RETURN 0; END $body$ LANGUAGE 'plpgsql'; 

| sql fiddle demo |