Enum type emulation: problem with opaque type in PL/pgSQL functions
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.
Max Fonin <fonin@ziet.zhitomir.ua> writes:
I guess the problem is that PL/pgSQL doesn't handle opaque type correctly.
No it doesn't, which is not surprising considering that opaque isn't
really a type at all. The error message could be improved though :-(
Currently I believe that the only way to write datatype I/O routines
is to do it in C, because what they really need to deal in is C-style
strings, and those are not an SQL-level type.
regards, tom lane
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.
In MySQL, ENUM is like this:
create table blah (
sex ENUM ('M', 'F')
);
This can be emulated in Postgres like this:
create table blah (
sex CHAR(1) CHECK (sex IN ('M', 'F'))
);
The _real_ trick is implementing MySQL sets in Postgres...
Chris
On Thu, 23 Nov 2000 11:13:28 -0500
Tom Lane <tgl@sss.pgh.pa.us> wrote:
Max Fonin <fonin@ziet.zhitomir.ua> writes:
I guess the problem is that PL/pgSQL doesn't handle opaque type correctly.
No it doesn't, which is not surprising considering that opaque isn't
really a type at all. The error message could be improved though :-(
Well, I understood that the C is the only way very quick.
Really, OPAQUE is just reference type like char* or void*, isn't it ?
OK, I implemented emulation and now have some working version at http://ziet.zhitomir.ua/~fonin/code/my2pg.pl.
This is MySQL->Postgres dump converter and I've succeed with loading my production MySQL database converted
with it to Postgres.
However it still needs manuall correction (see BUGS section in POD).
BTW, can't somebody tell me when PG 7.1 will be released :) ?
Currently I believe that the only way to write datatype I/O routines
is to do it in C, because what they really need to deal in is C-style
strings, and those are not an SQL-level type.regards, tom lane
Thanks,
Max Rudensky.
Max Fonin <fonin@ziet.zhitomir.ua> writes:
Tom Lane <tgl@sss.pgh.pa.us> wrote:
I guess the problem is that PL/pgSQL doesn't handle opaque type correctly.
No it doesn't, which is not surprising considering that opaque isn't
really a type at all. The error message could be improved though :-(
Well, I understood that the C is the only way very quick.
Really, OPAQUE is just reference type like char* or void*, isn't it ?
No, it isn't a type at all. Opaque really means, in essence, that
you're not saying what the function's arguments or result are.
There are several reasons for handling datatype I/O routines that way:
1. The actual argument types include C strings, which aren't an SQL
datatype.
2. The I/O routines for a new type have to be defined before you can
say CREATE TYPE, and thus they can't name their true input or result
type anyway.
3. We have some "generic" I/O routines like array_in and array_out,
which work for multiple datatypes and so can't be declared as taking
any specific datatype.
BTW, the existing declarations of I/O routines for built-in types are
pretty messy and inconsistent (in particular, a lot of them are declared
to take or return int4 when they do no such thing). This could be
cleaned up somewhat if we invented an SQL type name for "C string",
but I don't see any way around the other two points.
regards, tom lane