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.

score 8 · Accepted Answer · answered Dec 25 '13 at 18:14

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;

It works, nice peace of code, thank you very much. The idea about CASE looks simple, I feel myself little stupid, only the excuse for me is that i'm not familar with postgresql :). Thanks again. — Andrey, Dec 26 '13 at 02:19
hm-hm, on other hand I didnt know about quoted-unknown-type , so, may be I was not able to solve it myself... — Andrey, Dec 26 '13 at 02:21
@Richard Huxton Thank you 4 sharing this with us. I like it very much. Upvote for CASE. — OO7, Feb 18 '15 at 05:12

score 3 · Answer 2 · answered Jan 17 '16 at 20:02

3

Very late to the party here, but I would like to add that in this specific case it is enough to simply cast the varchar to text to prevent recursion.

CREATE FUNCTION dummy_cast(varchar) RETURNS t_tl AS $$
    SELECT ('' || $1)::t_tl;
$$ LANGUAGE SQL;

answered Jan 17 '16 at 20:02

Andreas Jarbol

695
2
9
26

score 0 · Answer 3 · answered May 02 '17 at 22:48

Alternatively you may register equal operator instead of define cast one. I did it for Java + MyBatis in similar situation:

CREATE FUNCTION type_user_state_with_text_equals(_a user_state, _b text)
    RETURNS boolean AS
    $func$
        SELECT _a = _b::user_state;
    $func$
    LANGUAGE SQL IMMUTABLE STRICT;

CREATE OPERATOR = (
    leftarg = user_state,
    rightarg = text,
    procedure = type_user_state_with_text_equals,
    COMMUTATOR = =,
    NEGATOR = !=,
    HASHES, MERGES
);

You may read about postgres user-defined operations in documentation and do not forget look at optimization hints.

postgresql: enum and character varying, updating

3 Answers3

Linked