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=''Female'' 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_gender" 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*******@cancer.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/xfFYB0Yt5lXqNn4RApV1AJ4tx2IQhCtQxMS+Hb34CrB5BhUMVQ CdEsWK
JVXphgxj6MyIj3x6a1TTGU4=
=AzZo
-----END PGP SIGNATURE-----
