CREATE TYPE in Postgres 7.3.4

[Alex Page]

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: [phone] (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-----

[Joe Conway]

Alex Page wrote:

[...creates enum_gender_in and enum_gender_out as PL/pgSQL functions...]

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:

According to the docs, you cannot use PL/pgSQL functions for I/O
conversion functions. See
http://www.postgresql.org/docs/curre...PGSQL-OVERVIEW
where it says:
"Except for input/output conversion and calculation functions for
user-defined types, anything that can be defined in C language
functions can also be done with PL/pgSQL."

In general, I don't think I/O functions can be anything other than C
functions.

On this page
http://www.postgresql.org/docs/curre...reatetype.html
it says:
"The support functions input_function and output_function are
required, while the functions receive_function and send_function are
optional. Generally these functions have to be coded in C or another
low-level language."

Joe
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Oliver Elphick]

On Thu, 2003-11-27 at 12:43, Alex Page wrote:

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

....

Why not just use a CHECK constraint?

CREATE TABLE xxx (
...
gender CHAR(1) CONSTRAINT "valid gender"
CHECK (gender IN ('M', 'F')),
...
);
--
Oliver Elphick Ol************@lfix.co.uk
Isle of Wight, UK http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
========================================
"But grow in grace, and in the knowledge of our Lord
and Saviour Jesus Christ. To him be glory both now and
for ever. Amen." II Peter 3:18
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

[Alex Page]

On Sun, Nov 30, 2003 at 09:01:01PM +0000, Oliver Elphick wrote:

On Thu, 2003-11-27 at 12:43, Alex Page wrote:

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

...

Why not just use a CHECK constraint?

I'm fairly sure that a CHECK constraint will be a lot slower (even with
an index) than my own TYPE with an internal integer representation. I'd
like to check this, but of course I can't do any benchmarks until I've
got the CREATE TYPE working :)

Alex
--
Mail: Alex Page <al*******@cancer.org.uk>
Real: Systems/Network Assistant, Epidemiology Unit, Oxford
Tel: [phone] (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/zMjDB0Yt5lXqNn4RAoZmAJ9mWSt18T1QuYKMk9Lqayb3BIV4Bw CffCQP
/oUxh5awE7nPdaxINz0SJck=
=jt1X
-----END PGP SIGNATURE-----

[Alex Page]

On Thu, Nov 27, 2003 at 07:48:46AM -0800, Joe Conway wrote:

Alex Page wrote: [...creates enum_gender_in and enum_gender_out as PL/pgSQL functions...] According to the docs, you cannot use PL/pgSQL functions for I/O
conversion functions. See
http://www.postgresql.org/docs/curre...PGSQL-OVERVIEW
where it says:
"Except for input/output conversion and calculation functions for
user-defined types, anything that can be defined in C language
functions can also be done with PL/pgSQL."

Fair enough; I was looking at the reference manual for 7.3, which
doesn't mention this limitation under either CREATE TYPE or CREATE
FUNCTION. This has changed in 7.4, where the documentation for CREATE
TYPE reads:

"The support functions input_function and output_function are required,
while the functions receive_function and send_function are optional.
Generally these functions have to be coded in C or another low-level
language."

Alex
--
Mail: Alex Page <al*******@cancer.org.uk>
Real: Systems/Network Assistant, Epidemiology Unit, Oxford
Tel: [phone] (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/zMueB0Yt5lXqNn4RAlblAJ4tfDUGvtxXjv4D9ZcvEMgV4SFpRA CeP6iC
0PoZYbOAOitPzC1JAyTu3UU=
=ENvb
-----END PGP SIGNATURE-----