Re: [HACKERS] Enum type emulation: problem with opaque type in PL/pgSQL functions

[Max Fonin]

On Thu, 23 Nov 2000 11:13:28 -0500
Tom Lane [EMAIL PROTECTED] wrote:

 Max Fonin [EMAIL PROTECTED] 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.

[HACKERS] Enum type emulation: problem with opaque type in PL/pgSQL functions

[Max Fonin]

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.