From 57690c6803525f879fe96920a05e979ece073e71 Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Mon, 2 Apr 2007 03:49:42 +0000 Subject: Support enum data types. Along the way, use macros for the values of pg_type.typtype whereever practical. Tom Dunstan, with some kibitzing from Tom Lane. --- doc/src/sgml/catalogs.sgml | 74 +++++++++++++--- doc/src/sgml/datatype.sgml | 172 +++++++++++++++++++++++++++++++++++++- doc/src/sgml/extend.sgml | 16 ++-- doc/src/sgml/func.sgml | 83 +++++++++++++++++- doc/src/sgml/plpgsql.sgml | 12 +-- doc/src/sgml/ref/create_type.sgml | 45 +++++++++- doc/src/sgml/xfunc.sgml | 12 +-- 7 files changed, 380 insertions(+), 34 deletions(-) (limited to 'doc/src') diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml index 7d325d1dde5..492b06de0a2 100644 --- a/doc/src/sgml/catalogs.sgml +++ b/doc/src/sgml/catalogs.sgml @@ -1,4 +1,4 @@ - + @@ -128,6 +128,11 @@ descriptions or comments on database objects + + pg_enum + enum label and value definitions + + pg_index additional index information @@ -1425,11 +1430,7 @@ in which the source and target types are the same, if the associated function takes more than one argument. Such entries represent length coercion functions that coerce values of the type - to be legal for a particular type modifier value. Note however that - at present there is no support for associating non-default type - modifiers with user-created data types, and so this facility is only - of use for the small number of built-in types that have type modifier - syntax built into the grammar. + to be legal for a particular type modifier value. @@ -2413,6 +2414,55 @@ + + <structname>pg_enum</structname> + + + pg_enum + + + + The pg_enum catalog contains entries + matching enum types to their associated values and labels. The + internal representation of a given enum value is actually the OID + of its associated row in pg_enum. The + OIDs for a particular enum type are guaranteed to be ordered in + the way the type should sort, but there is no guarantee about the + ordering of OIDs of unrelated enum types. + + + + <structname>pg_enum</> Columns + + + + + Name + Type + References + Description + + + + + + enumtypid + oid + pg_type.oid + The OID of the pg_type entry owning this enum value + + + + enumlabel + name + + The textual label for this enum value + + + +
+ + <structname>pg_index</structname> @@ -4395,11 +4445,13 @@ char - typtype is b for - a base type, c for a composite type (e.g., a - table's row type), d for a domain, or - p for a pseudo-type. See also - typrelid and + typtype is + b for a base type, + c for a composite type (e.g., a table's row type), + d for a domain, + e for an enum type, + or p for a pseudo-type. + See also typrelid and typbasetype diff --git a/doc/src/sgml/datatype.sgml b/doc/src/sgml/datatype.sgml index 2d67885fd65..1bf103c8780 100644 --- a/doc/src/sgml/datatype.sgml +++ b/doc/src/sgml/datatype.sgml @@ -1,4 +1,4 @@ - + Data Types @@ -2424,6 +2424,161 @@ SELECT * FROM test1 WHERE a; + + Enumerated Types + + + data type + enumerated (enum) + + + + Enumerated (enum) types are data types that + are comprised of a static, predefined set of values with a + specific order. They are equivalent to the enum + types in a number of programming languages. An example of an enum + type might be the days of the week, or a set of status values for + a piece of data. + + + + Declaration of Enumerated Types + + + Enum types are created using the command, + for example: + + +CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy'); + + + Once created, the enum type can be used in table and function + definitions much like any other type: + + + + Basic Enum Usage + +CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy'); +CREATE TABLE person ( + name text, + current_mood mood +); +INSERT INTO person VALUES ('Moe', 'happy'); +SELECT * FROM person WHERE current_mood = 'happy'; + name | current_mood +------+-------------- + Moe | happy +(1 row) + + + + + + Ordering + + + The ordering of the values in an enum type is the + order in which the values were listed when the type was declared. + All standard comparison operators and related + aggregate functions are supported for enums. For example: + + + + Enum Ordering + +INSERT INTO person VALUES ('Larry', 'sad'); +INSERT INTO person VALUES ('Curly', 'ok'); +SELECT * FROM person WHERE current_mood > 'sad'; + name | current_mood +-------+-------------- + Moe | happy + Curly | ok +(2 rows) + +SELECT * FROM person WHERE current_mood > 'sad' ORDER BY current_mood; + name | current_mood +-------+-------------- + Curly | ok + Moe | happy +(2 rows) + +SELECT name FROM person + WHERE current_mood = (SELECT MIN(current_mood) FROM person); + name +------- + Larry +(1 row) + + + + + + Type Safety + + + Enumerated types are completely separate data types and may not + be compared with each other. + + + + Lack of Casting + +CREATE TYPE happiness AS ENUM ('happy', 'very happy', 'ecstatic'); +CREATE TABLE holidays ( + num_weeks int, + happiness happiness +); +INSERT INTO holidays(num_weeks,happiness) VALUES (4, 'happy'); +INSERT INTO holidays(num_weeks,happiness) VALUES (6, 'very happy'); +INSERT INTO holidays(num_weeks,happiness) VALUES (8, 'ecstatic'); +INSERT INTO holidays(num_weeks,happiness) VALUES (2, 'sad'); +ERROR: invalid input value for enum happiness: "sad" +SELECT person.name, holidays.num_weeks FROM person, holidays + WHERE person.current_mood = holidays.happiness; +ERROR: operator does not exist: mood = happiness + + + + + If you really need to do something like that, you can either + write a custom operator or add explicit casts to your query: + + + + Comparing Different Enums by Casting to Text + +SELECT person.name, holidays.num_weeks FROM person, holidays + WHERE person.current_mood::text = holidays.happiness::text; + name | num_weeks +------+----------- + Moe | 4 +(1 row) + + + + + + + Implementation Details + + + An enum value occupies four bytes on disk. The length of an enum + value's textual label is limited by the NAMEDATALEN + setting compiled into PostgreSQL; in standard + builds this means at most 63 bytes. + + + + Enum labels are case sensitive, so + 'happy' is not the same as 'HAPPY'. + Spaces in the labels are significant, too. + + + + + Geometric Types @@ -3278,6 +3433,10 @@ SELECT * FROM pg_attribute anyelement + + anyenum + + void @@ -3343,6 +3502,13 @@ SELECT * FROM pg_attribute (see ). + + anyenum + Indicates that a function accepts any enum data type + (see and + ). + + cstring Indicates that a function accepts or returns a null-terminated C string. @@ -3395,8 +3561,8 @@ SELECT * FROM pg_attribute languages all forbid use of a pseudo-type as argument type, and allow only void and record as a result type (plus trigger when the function is used as a trigger). Some also - support polymorphic functions using the types anyarray and - anyelement. + support polymorphic functions using the types anyarray, + anyelement and anyenum. diff --git a/doc/src/sgml/extend.sgml b/doc/src/sgml/extend.sgml index ad22a5dcddd..bb5834e74a9 100644 --- a/doc/src/sgml/extend.sgml +++ b/doc/src/sgml/extend.sgml @@ -1,4 +1,4 @@ - + Extending <acronym>SQL</acronym> @@ -193,9 +193,10 @@ - Two pseudo-types of special interest are anyelement and - anyarray, which are collectively called polymorphic - types. Any function declared using these types is said to be + Three pseudo-types of special interest are anyelement, + anyarray, and anyenum, + which are collectively called polymorphic types. + Any function declared using these types is said to be a polymorphic function. A polymorphic function can operate on many different data types, with the specific data type(s) being determined by the data types actually passed to it in a particular @@ -215,6 +216,9 @@ anyelement, the actual array type in the anyarray positions must be an array whose elements are the same type appearing in the anyelement positions. + anyenum is treated exactly the same as anyelement, + but adds the additional constraint that the actual type must + be an enum type. @@ -234,7 +238,9 @@ implements subscripting as subscript(anyarray, integer) returns anyelement. This declaration constrains the actual first argument to be an array type, and allows the parser to infer the correct - result type from the actual first argument's type. + result type from the actual first argument's type. Another example + is that a function declared as f(anyarray) returns anyenum + will only accept arrays of enum types. diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index db7cd1d1f3b..0baf1525944 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -1,4 +1,4 @@ - + Functions and Operators @@ -6646,6 +6646,87 @@ SELECT pg_sleep(1.5); + + Enum Support Functions + + + For enum types (described in ), + there are several functions that allow cleaner programming without + hard-coding particular values of an enum type. + These are listed in . The examples + assume an enum type created as: + + +CREATE TYPE rainbow AS ENUM ('red', 'orange', 'yellow', 'green', 'blue', 'purple'); + + + + + + Enum Support Functions + + + + Function + Description + Example + Example Result + + + + + enum_first(anyenum) + Returns the first value of the input enum type + enum_first(null::rainbow) + red + + + enum_last(anyenum) + Returns the last value of the input enum type + enum_last(null::rainbow) + purple + + + enum_range(anyenum) + Returns all values of the input enum type in an ordered array + enum_range(null::rainbow) + {red,orange,yellow,green,blue,purple} + + + enum_range(anyenum, anyenum) + + Returns the range between the two given enum values, as an ordered + array. The values must be from the same enum type. If the first + parameter is null, the result will start with the first value of + the enum type. + If the second parameter is null, the result will end with the last + value of the enum type. + + enum_range('orange'::rainbow, 'green'::rainbow) + {orange,yellow,green} + + + enum_range(NULL, 'green'::rainbow) + {red,orange,yellow,green} + + + enum_range('orange'::rainbow, NULL) + {orange,yellow,green,blue,purple} + + + +
+ + + Notice that except for the two-argument form of enum_range, + these functions disregard the specific value passed to them; they care + only about its declared datatype. Either NULL or a specific value of + the type can be passed, with the same result. It is more common to + apply these functions to a table column or function argument than to + a hardwired type name as suggested by the examples. + + + Geometric Functions and Operators diff --git a/doc/src/sgml/plpgsql.sgml b/doc/src/sgml/plpgsql.sgml index 55904d6638a..e6f7309c650 100644 --- a/doc/src/sgml/plpgsql.sgml +++ b/doc/src/sgml/plpgsql.sgml @@ -1,4 +1,4 @@ - + <application>PL/pgSQL</application> - <acronym>SQL</acronym> Procedural Language @@ -210,7 +210,8 @@ $$ LANGUAGE plpgsql; PL/pgSQL functions can also be declared to accept and return the polymorphic types - anyelement and anyarray. The actual + anyelement, anyarray, and anyenum. + The actual data types handled by a polymorphic function can vary from call to call, as discussed in . An example is shown in . @@ -698,8 +699,9 @@ $$ LANGUAGE plpgsql; When the return type of a PL/pgSQL - function is declared as a polymorphic type (anyelement - or anyarray), a special parameter $0 + function is declared as a polymorphic type (anyelement, + anyarray, or anyenum), + a special parameter $0 is created. Its data type is the actual return type of the function, as deduced from the actual input types (see ). @@ -726,7 +728,7 @@ $$ LANGUAGE plpgsql; The same effect can be had by declaring one or more output parameters as - anyelement or anyarray. In this case the + polymorphic types. In this case the special $0 parameter is not used; the output parameters themselves serve the same purpose. For example: diff --git a/doc/src/sgml/ref/create_type.sgml b/doc/src/sgml/ref/create_type.sgml index 29f35417079..9be57d7fcde 100644 --- a/doc/src/sgml/ref/create_type.sgml +++ b/doc/src/sgml/ref/create_type.sgml @@ -1,5 +1,5 @@ @@ -23,6 +23,9 @@ PostgreSQL documentation CREATE TYPE name AS ( attribute_name data_type [, ... ] ) +CREATE TYPE name AS ENUM + ( 'label' [, ... ] ) + CREATE TYPE name ( INPUT = input_function, OUTPUT = output_function @@ -77,11 +80,23 @@ CREATE TYPE name + + Enumerated Types + + + The second form of CREATE TYPE creates an enumerated + (enum) type, as described in . + Enum types take a list of one or more quoted labels, each of which + must be less than NAMEDATALEN bytes long (64 in a standard + PostgreSQL build). + + + Base Types - The second form of CREATE TYPE creates a new base type + The third form of CREATE TYPE creates a new base type (scalar type). The parameters can appear in any order, not only that illustrated above, and most are optional. You must register two or more functions (using CREATE FUNCTION) before @@ -297,7 +312,7 @@ CREATE TYPE name Array Types - Whenever a user-defined base data type is created, + Whenever a user-defined base or enum data type is created, PostgreSQL automatically creates an associated array type, whose name consists of the base type's name prepended with an underscore. The parser understands this @@ -363,6 +378,16 @@ CREATE TYPE name + + label + + + A string literal representing the textual label associated with + one value of an enum type. + + + + input_function @@ -567,6 +592,20 @@ $$ LANGUAGE SQL; + + This example creates an enumerated type and uses it in + a table definition: + +CREATE TYPE bug_status AS ENUM ('new', 'open', 'closed'); + +CREATE TABLE bug ( + serial id, + description text, + status bug_status +); + + + This example creates the base data type box and then uses the type in a table definition: diff --git a/doc/src/sgml/xfunc.sgml b/doc/src/sgml/xfunc.sgml index 65d636b5e4c..553b33e1732 100644 --- a/doc/src/sgml/xfunc.sgml +++ b/doc/src/sgml/xfunc.sgml @@ -1,4 +1,4 @@ - + User-Defined Functions @@ -717,8 +717,8 @@ SELECT name, listchildren(name) FROM nodes; SQL functions can be declared to accept and - return the polymorphic types anyelement and - anyarray. See anyelement, + anyarray, and anyenum. See for a more detailed explanation of polymorphic functions. Here is a polymorphic function make_array that builds up an array @@ -746,7 +746,7 @@ SELECT make_array(1, 2) AS intarray, make_array('a'::text, 'b') AS textarray; Without the typecast, you will get errors like this: -ERROR: could not determine "anyarray"/"anyelement" type because input has type "unknown" +ERROR: could not determine polymorphic type because input has type "unknown" @@ -769,7 +769,7 @@ CREATE FUNCTION invalid_func() RETURNS anyelement AS $$ SELECT 1; $$ LANGUAGE SQL; ERROR: cannot determine result data type -DETAIL: A function returning "anyarray" or "anyelement" must have at least one argument of either type. +DETAIL: A function returning a polymorphic type must have at least one polymorphic argument. @@ -2831,7 +2831,7 @@ CREATE OR REPLACE FUNCTION retcomposite(IN integer, IN integer, C-language functions can be declared to accept and return the polymorphic types - anyelement and anyarray. + anyelement, anyarray, and anyenum. See for a more detailed explanation of polymorphic functions. When function arguments or return types are defined as polymorphic types, the function author cannot know -- cgit v1.2.3