From: | Max Fonin <fonin(at)ziet(dot)zhitomir(dot)ua> |
---|---|
To: | hackers(at)postgresql(dot)org |
Cc: | pgsql-general(at)postgresql(dot)org, pgsql-hackers(at)postgresql(dot)org |
Subject: | Enum type emulation: problem with opaque type in PL/pgSQL functions |
Date: | 2000-11-16 19:24:20 |
Message-ID: | 20001116212420.1b9f6233.fonin@ziet.zhitomir.ua |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general pgsql-hackers |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Matthew | 2000-11-16 19:27:20 | RE: [HACKERS] Re: PHPBuilder article -- Postgres vs My SQL |
Previous Message | Marc SCHAEFER | 2000-11-16 19:08:27 | Re: Datestyle and Postmaster |
From | Date | Subject | |
---|---|---|---|
Next Message | Don Baccus | 2000-11-16 19:36:34 | Re: RE: [COMMITTERS] pgsql/src/backend/access/transam ( xact.c xlog.c) |
Previous Message | Bruce Momjian | 2000-11-16 19:13:04 | Re: RE: [COMMITTERS] pgsql/src/backend/access/transam ( xact.c xlog.c) |