Thread: CREATE TYPE in Postgres 7.3.4
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 <alex.page@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
Attachment
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/current/static/plpgsql.html#PLPGSQL-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/current/static/sql-createtype.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
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 Oliver.Elphick@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
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 <alex.page@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
Attachment
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/current/static/plpgsql.html#PLPGSQL-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 <alex.page@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