This is probably really basic, but I can't seem to get it to work. I'm
trying to create an enumerated type, using the following code:
CREATE FUNCTION enum_gender_in (cstring) RETURNS enum_gender IMMUTABLE AS
'DECLARE invalue ALIAS FOR $1;
BEGIN
IF invalue=''Male' ' OR invalue=''0'' THEN RETURN 0; END IF;
IF invalue=''Femal e'' OR invalue=''1'' THEN RETURN 1; END IF;
RAISE EXCEPTION ''Incorrect input value: %'', invalue;
END;'
LANGUAGE 'plpgsql';
CREATE FUNCTION enum_gender_out (enum_gender) RETURNS cstring IMMUTABLE AS
'DECLARE outvalue ALIAS FOR $1;
BEGIN
IF outvalue=0 THEN RETURN ''Male''; END IF;
IF outvalue=1 THEN RETURN ''Female''; END IF;
RAISE EXCEPTION ''Incorrect output value: %'', outvalue;
END;'
LANGUAGE 'plpgsql';
CREATE TYPE enum_gender (
INPUT = enum_gender_in,
OUTPUT = enum_gender_out ,
INTERNALLENGTH = 2,
PASSEDBYVALUE
);
According to the Postgres documentation, when I create the input
function, it should create a placeholder entry in pg_type for
enum_gender and wait for the type to be created. However, when I execute
the CREATE FUNCTION statement, I get:
ERROR: Type "enum_gende r" does not exist
If I try to create the type first, I get:
ERROR: TypeCreate: function enum_gender_in( cstring) does not exist
How do I create this type and these functions?
Alex
--
Mail: Alex Page <al*******@canc er.org.uk>
Real: Systems/Network Assistant, Epidemiology Unit, Oxford
Tel: 01865 302 223 (external) / 223 (internal)
PGP: 8868 21D7 3D35 DD77 9D06 BF0A 0746 2DE6 55EA 367E
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.3 (GNU/Linux)
iD8DBQE/xfFYB0Yt5lXqNn4 RApV1AJ4tx2IQhC tQxMS+Hb34CrB5B hUMVQCdEsWK
JVXphgxj6MyIj3x 6a1TTGU4=
=AzZo
-----END PGP SIGNATURE-----
