Enum type emulation: problem with opaque type in PL/pgSQL functions
От | Max Fonin |
---|---|
Тема | Enum type emulation: problem with opaque type in PL/pgSQL functions |
Дата | |
Msg-id | 20001116212420.1b9f6233.fonin@ziet.zhitomir.ua обсуждение исходный текст |
Ответы |
Re: [HACKERS] Enum type emulation: problem with opaque type in PL/pgSQL functions
RE: [HACKERS] Enum type emulation: problem with opaque type in PL/pgSQL functions |
Список | pgsql-general |
Guys, hello. Here is a problem. -- -- Creating 2 new functions and new type -- BEGIN; CREATE FUNCTION enum_week_in (opaque) RETURNS int2 AS ' DECLARE invalue ALIAS for $1; BEGIN IF invalue='''' OR invalue=''0'' THEN RETURN 0; END IF; IF invalue=''Monday'' OR invalue=''1'' THEN RETURN 1; END IF; IF invalue=''Tuesday'' OR invalue=''2'' THEN RETURN 2; END IF; IF invalue=''Wednesday'' OR invalue=''3'' THEN RETURN 3; END IF; RAISE EXCEPTION ''incorrect input value: %'',invalue; END;' LANGUAGE 'plpgsql' WITH (ISCACHABLE); CREATE FUNCTION enum_week_out (opaque) RETURNS text AS ' DECLARE outvalue ALIAS for $1; BEGIN IF outvalue=0 THEN RETURN ''''; END IF; IF outvalue=1 THEN RETURN ''Monday''; END IF; IF outvalue=2 THEN RETURN ''Tuesday''; END IF; IF outvalue=3 THEN RETURN ''Wednesday''; END IF; RAISE EXCEPTION ''incorrect output value: %'',outvalue; END;' LANGUAGE 'plpgsql' WITH (ISCACHABLE); CREATE TYPE enum_week ( internallength = 2, input = enum_week_in, output = enum_week_out, PASSEDBYVALUE ); COMMIT; Well, all is ok after it, e.g. functions and type were registered in system catalog. Now, when I try to do "SELECT enum_week_in('Monday')", I get the following: NOTICE: plpgsql: ERROR during compile of enum_week_in near line 0 The same will occure if I CREATE TABLE test (wday enum_week); insert into test (wday) values ('Monday') If I redefine the same functions with input argtype 'text'/'int2' they work fine. I guess the problem is that PL/pgSQL doesn't handle opaque type correctly. Any ideas ? I don't care how but I need to emulate ENUM type, just to convert MySQL dumps to PostgreSQL. E.g. ENUM values stored in MySQL dump should be restorable in Postgres without any conversion. I running PostgreSQL 7.0.3 on Linux RedHat 6.2, kernel 2.2.15, Intel Celeron CPU; Postgres was upgraded from 7.0.2 without changing anything in system catalog. Thanks, Max Rudensky.
В списке pgsql-general по дате отправления: