postgresql: enum and character varying, updating

Question

I have the ENUM type in postgresql

CREATE TYPE user_state AS ENUM ('unconfirmed', 'locked', 'active');

I have a field state in table users with the type user_state.

And I try to execute the next query:

UPDATE "users" SET "state" = 'active'::character varying WHERE "id" = 1 (*1)

But the query fails. The error is: Column "state" is of type user_state but expression is of type character varying. Hint: You will need to rewrite or cast the expression.

Okay, there were a little bit of reading mans, a writing a little bit of code, and I have implicit type cast for character varying to user_state:

CREATE OR REPLACE FUNCTION
    character_varying_to_user_status(user_state)
    RETURNS user_state AS $$
    SELECT $1::user_state
    $$ LANGUAGE SQL;

CREATE CAST (character varying AS user_state)
    WITH FUNCTION character_varying_to_user_status (character varying)
    AS IMPLICIT

But this does not work. It works only in case if id does not exist (so, query does nothing, so, syntax and type checking are correct now I suppose). But if id exists, postgresql says me that stack is overflowed (that is why I'm on this site now, ha-ha). The error is:

ERROR:  stack depth limit exceeded
HINT:  Increase the configuration parameter "max_stack_depth" (currently 2048kB), after ensuring the platform's stack depth limit is adequate.
CONTEXT:  SQL function "character_varying_to_user_status" during inlining
SQL function "character_varying_to_user_status" during startup
SQL function "character_varying_to_user_status" statement 1
SQL function "character_varying_to_user_status" statement 1
SQL function "character_varying_to_user_status" statement 1
...
...
SQL function "character_varying_to_user_status" statement 1

In operator:
UPDATE "users" SET "state" = 'unconfirmed'::character varying WHERE "id" = 8

And I have no idea how to fix it. Any ideas?

Postgresql 9.1 and lift-squeryl-record (of version 2.5).

(*1) The query UPDATE "users" SET "state" = 'active'::character varying WHERE "id" = 1 is not a real one. I use squeryl (scala-language orm), and the squeryl generates the query, so, I can't just remove ::character varying to have it work. Real query looks like this (in error answer): update "users" set "state" = ? where "id" = ? jdbcParams:[active,10] and answers to me exactly as I wrote in question above (about rewrite or cast the expression). So, the query is given as is and I cant change it: I can't just remove ::character varying or add ::user_state or CAST(.. as ..).

UPD. Could also somebody try run code on a newer version of postgresql (9.2, 9.3, 9.4)? If it works, it is the answer too.

Answer 1

The problem with a simple attempt is you have a cast calling the cast, calling the cast, calling the cast...

You need somehow to get away from varchar->enum in your cast. Simplest (most understandable) way is to just manually convert. Note the string literals being cast in the case statement aren't text they are quoted-unknown-type which sidesteps the infinite recursion.

BEGIN;

CREATE TYPE t_tl AS ENUM ('red', 'amber', 'green');

CREATE FUNCTION dummy_cast(varchar) RETURNS t_tl AS $$
    SELECT CASE $1
        WHEN 'red' THEN 'red'::t_tl
        WHEN 'amber' THEN 'amber'::t_tl
        WHEN 'green' THEN 'green'::t_tl
    END;
$$ LANGUAGE SQL;

CREATE CAST (varchar AS t_tl) WITH FUNCTION dummy_cast(varchar) AS ASSIGNMENT;

CREATE TABLE t (x t_tl);

INSERT INTO t VALUES ('red');
INSERT INTO t VALUES ('green'::varchar);

SELECT * FROM t;

ROLLBACK;