Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit57690c6

Browse files
committed
Support enum data types. Along the way, use macros for the values of
pg_type.typtype whereever practical. Tom Dunstan, with some kibitzingfrom Tom Lane.
1 parenta482a3e commit57690c6

File tree

74 files changed

+2398
-332
lines changed

Some content is hidden

Large Commits have some content hidden by default. Use the searchbox below for content that may be hidden.

74 files changed

+2398
-332
lines changed

‎doc/src/sgml/catalogs.sgml

Lines changed: 63 additions & 11 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
<!-- $PostgreSQL: pgsql/doc/src/sgml/catalogs.sgml,v 2.148 2007/03/26 16:58:37 tgl Exp $ -->
1+
<!-- $PostgreSQL: pgsql/doc/src/sgml/catalogs.sgml,v 2.149 2007/04/02 03:49:36 tgl Exp $ -->
22
<!--
33
Documentation of the system catalogs, directed toward PostgreSQL developers
44
-->
@@ -128,6 +128,11 @@
128128
<entry>descriptions or comments on database objects</entry>
129129
</row>
130130

131+
<row>
132+
<entry><link linkend="catalog-pg-enum"><structname>pg_enum</structname></link></entry>
133+
<entry>enum label and value definitions</entry>
134+
</row>
135+
131136
<row>
132137
<entry><link linkend="catalog-pg-index"><structname>pg_index</structname></link></entry>
133138
<entry>additional index information</entry>
@@ -1425,11 +1430,7 @@
14251430
in which the source and target types are the same, if the associated
14261431
function takes more than one argument. Such entries represent
14271432
<quote>length coercion functions</> that coerce values of the type
1428-
to be legal for a particular type modifier value. Note however that
1429-
at present there is no support for associating non-default type
1430-
modifiers with user-created data types, and so this facility is only
1431-
of use for the small number of built-in types that have type modifier
1432-
syntax built into the grammar.
1433+
to be legal for a particular type modifier value.
14331434
</para>
14341435

14351436
<para>
@@ -2413,6 +2414,55 @@
24132414
</sect1>
24142415

24152416

2417+
<sect1 id="catalog-pg-enum">
2418+
<title><structname>pg_enum</structname></title>
2419+
2420+
<indexterm zone="catalog-pg-enum">
2421+
<primary>pg_enum</primary>
2422+
</indexterm>
2423+
2424+
<para>
2425+
The <structname>pg_enum</structname> catalog contains entries
2426+
matching enum types to their associated values and labels. The
2427+
internal representation of a given enum value is actually the OID
2428+
of its associated row in <structname>pg_enum</structname>. The
2429+
OIDs for a particular enum type are guaranteed to be ordered in
2430+
the way the type should sort, but there is no guarantee about the
2431+
ordering of OIDs of unrelated enum types.
2432+
</para>
2433+
2434+
<table>
2435+
<title><structname>pg_enum</> Columns</title>
2436+
2437+
<tgroup cols=4>
2438+
<thead>
2439+
<row>
2440+
<entry>Name</entry>
2441+
<entry>Type</entry>
2442+
<entry>References</entry>
2443+
<entry>Description</entry>
2444+
</row>
2445+
</thead>
2446+
2447+
<tbody>
2448+
<row>
2449+
<entry><structfield>enumtypid</structfield></entry>
2450+
<entry><type>oid</type></entry>
2451+
<entry><literal><link linkend="catalog-pg-type"><structname>pg_type</structname></link>.oid</literal></entry>
2452+
<entry>The OID of the <structname>pg_type</> entry owning this enum value</entry>
2453+
</row>
2454+
2455+
<row>
2456+
<entry><structfield>enumlabel</structfield></entry>
2457+
<entry><type>name</type></entry>
2458+
<entry></entry>
2459+
<entry>The textual label for this enum value</entry>
2460+
</row>
2461+
</tbody>
2462+
</tgroup>
2463+
</table>
2464+
</sect1>
2465+
24162466
<sect1 id="catalog-pg-index">
24172467
<title><structname>pg_index</structname></title>
24182468

@@ -4395,11 +4445,13 @@
43954445
<entry><type>char</type></entry>
43964446
<entry></entry>
43974447
<entry>
4398-
<structfield>typtype</structfield> is <literal>b</literal> for
4399-
a base type, <literal>c</literal> for a composite type (e.g., a
4400-
table's row type), <literal>d</literal> for a domain, or
4401-
<literal>p</literal> for a pseudo-type. See also
4402-
<structfield>typrelid</structfield> and
4448+
<structfield>typtype</structfield> is
4449+
<literal>b</literal> for a base type,
4450+
<literal>c</literal> for a composite type (e.g., a table's row type),
4451+
<literal>d</literal> for a domain,
4452+
<literal>e</literal> for an enum type,
4453+
or <literal>p</literal> for a pseudo-type.
4454+
See also <structfield>typrelid</structfield> and
44034455
<structfield>typbasetype</structfield>
44044456
</entry>
44054457
</row>

‎doc/src/sgml/datatype.sgml

Lines changed: 169 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
<!-- $PostgreSQL: pgsql/doc/src/sgml/datatype.sgml,v 1.191 2007/03/14 17:38:05 tgl Exp $ -->
1+
<!-- $PostgreSQL: pgsql/doc/src/sgml/datatype.sgml,v 1.192 2007/04/02 03:49:36 tgl Exp $ -->
22

33
<chapter id="datatype">
44
<title id="datatype-title">Data Types</title>
@@ -2424,6 +2424,161 @@ SELECT * FROM test1 WHERE a;
24242424
</para>
24252425
</sect1>
24262426

2427+
<sect1 id="datatype-enum">
2428+
<title>Enumerated Types</title>
2429+
2430+
<indexterm zone="datatype-enum">
2431+
<primary>data type</primary>
2432+
<secondary>enumerated (enum)</secondary>
2433+
</indexterm>
2434+
2435+
<para>
2436+
Enumerated (enum) types are data types that
2437+
are comprised of a static, predefined set of values with a
2438+
specific order. They are equivalent to the <type>enum</type>
2439+
types in a number of programming languages. An example of an enum
2440+
type might be the days of the week, or a set of status values for
2441+
a piece of data.
2442+
</para>
2443+
2444+
<sect2>
2445+
<title>Declaration of Enumerated Types</title>
2446+
2447+
<para>
2448+
Enum types are created using the <xref
2449+
linkend="sql-createtype" endterm="sql-createtype-title"> command,
2450+
for example:
2451+
2452+
<programlisting>
2453+
CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy');
2454+
</programlisting>
2455+
2456+
Once created, the enum type can be used in table and function
2457+
definitions much like any other type:
2458+
</para>
2459+
2460+
<example>
2461+
<title>Basic Enum Usage</title>
2462+
<programlisting>
2463+
CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy');
2464+
CREATE TABLE person (
2465+
name text,
2466+
current_mood mood
2467+
);
2468+
INSERT INTO person VALUES ('Moe', 'happy');
2469+
SELECT * FROM person WHERE current_mood = 'happy';
2470+
name | current_mood
2471+
------+--------------
2472+
Moe | happy
2473+
(1 row)
2474+
</programlisting>
2475+
</example>
2476+
</sect2>
2477+
2478+
<sect2>
2479+
<title>Ordering</title>
2480+
2481+
<para>
2482+
The ordering of the values in an enum type is the
2483+
order in which the values were listed when the type was declared.
2484+
All standard comparison operators and related
2485+
aggregate functions are supported for enums. For example:
2486+
</para>
2487+
2488+
<example>
2489+
<title>Enum Ordering</title>
2490+
<programlisting>
2491+
INSERT INTO person VALUES ('Larry', 'sad');
2492+
INSERT INTO person VALUES ('Curly', 'ok');
2493+
SELECT * FROM person WHERE current_mood > 'sad';
2494+
name | current_mood
2495+
-------+--------------
2496+
Moe | happy
2497+
Curly | ok
2498+
(2 rows)
2499+
2500+
SELECT * FROM person WHERE current_mood > 'sad' ORDER BY current_mood;
2501+
name | current_mood
2502+
-------+--------------
2503+
Curly | ok
2504+
Moe | happy
2505+
(2 rows)
2506+
2507+
SELECT name FROM person
2508+
WHERE current_mood = (SELECT MIN(current_mood) FROM person);
2509+
name
2510+
-------
2511+
Larry
2512+
(1 row)
2513+
</programlisting>
2514+
</example>
2515+
</sect2>
2516+
2517+
<sect2>
2518+
<title>Type Safety</title>
2519+
2520+
<para>
2521+
Enumerated types are completely separate data types and may not
2522+
be compared with each other.
2523+
</para>
2524+
2525+
<example>
2526+
<title>Lack of Casting</title>
2527+
<programlisting>
2528+
CREATE TYPE happiness AS ENUM ('happy', 'very happy', 'ecstatic');
2529+
CREATE TABLE holidays (
2530+
num_weeks int,
2531+
happiness happiness
2532+
);
2533+
INSERT INTO holidays(num_weeks,happiness) VALUES (4, 'happy');
2534+
INSERT INTO holidays(num_weeks,happiness) VALUES (6, 'very happy');
2535+
INSERT INTO holidays(num_weeks,happiness) VALUES (8, 'ecstatic');
2536+
INSERT INTO holidays(num_weeks,happiness) VALUES (2, 'sad');
2537+
ERROR: invalid input value for enum happiness: "sad"
2538+
SELECT person.name, holidays.num_weeks FROM person, holidays
2539+
WHERE person.current_mood = holidays.happiness;
2540+
ERROR: operator does not exist: mood = happiness
2541+
</programlisting>
2542+
</example>
2543+
2544+
<para>
2545+
If you really need to do something like that, you can either
2546+
write a custom operator or add explicit casts to your query:
2547+
</para>
2548+
2549+
<example>
2550+
<title>Comparing Different Enums by Casting to Text</title>
2551+
<programlisting>
2552+
SELECT person.name, holidays.num_weeks FROM person, holidays
2553+
WHERE person.current_mood::text = holidays.happiness::text;
2554+
name | num_weeks
2555+
------+-----------
2556+
Moe | 4
2557+
(1 row)
2558+
2559+
</programlisting>
2560+
</example>
2561+
</sect2>
2562+
2563+
<sect2>
2564+
<title>Implementation Details</title>
2565+
2566+
<para>
2567+
An enum value occupies four bytes on disk. The length of an enum
2568+
value's textual label is limited by the <symbol>NAMEDATALEN</symbol>
2569+
setting compiled into <productname>PostgreSQL</productname>; in standard
2570+
builds this means at most 63 bytes.
2571+
</para>
2572+
2573+
<para>
2574+
Enum labels are case sensitive, so
2575+
<type>'happy'</type> is not the same as <type>'HAPPY'</type>.
2576+
Spaces in the labels are significant, too.
2577+
</para>
2578+
2579+
</sect2>
2580+
</sect1>
2581+
24272582
<sect1 id="datatype-geometric">
24282583
<title>Geometric Types</title>
24292584

@@ -3278,6 +3433,10 @@ SELECT * FROM pg_attribute
32783433
<primary>anyelement</primary>
32793434
</indexterm>
32803435

3436+
<indexterm zone="datatype-pseudo">
3437+
<primary>anyenum</primary>
3438+
</indexterm>
3439+
32813440
<indexterm zone="datatype-pseudo">
32823441
<primary>void</primary>
32833442
</indexterm>
@@ -3343,6 +3502,13 @@ SELECT * FROM pg_attribute
33433502
(see <xref linkend="extend-types-polymorphic">).</entry>
33443503
</row>
33453504

3505+
<row>
3506+
<entry><type>anyenum</></entry>
3507+
<entry>Indicates that a function accepts any enum data type
3508+
(see <xref linkend="extend-types-polymorphic"> and
3509+
<xref linkend="datatype-enum">).</entry>
3510+
</row>
3511+
33463512
<row>
33473513
<entry><type>cstring</></entry>
33483514
<entry>Indicates that a function accepts or returns a null-terminated C string.</entry>
@@ -3395,8 +3561,8 @@ SELECT * FROM pg_attribute
33953561
languages all forbid use of a pseudo-type as argument type, and allow
33963562
only <type>void</> and <type>record</> as a result type (plus
33973563
<type>trigger</> when the function is used as a trigger). Some also
3398-
support polymorphic functions using the types <type>anyarray</> and
3399-
<type>anyelement</>.
3564+
support polymorphic functions using the types <type>anyarray</>,
3565+
<type>anyelement</> and <type>anyenum</>.
34003566
</para>
34013567

34023568
<para>

‎doc/src/sgml/extend.sgml

Lines changed: 11 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
<!-- $PostgreSQL: pgsql/doc/src/sgml/extend.sgml,v 1.33 2007/01/31 20:56:17 momjian Exp $ -->
1+
<!-- $PostgreSQL: pgsql/doc/src/sgml/extend.sgml,v 1.34 2007/04/02 03:49:36 tgl Exp $ -->
22

33
<chapter id="extend">
44
<title>Extending <acronym>SQL</acronym></title>
@@ -193,9 +193,10 @@
193193
</indexterm>
194194

195195
<para>
196-
Two pseudo-types of special interest are <type>anyelement</> and
197-
<type>anyarray</>, which are collectively called <firstterm>polymorphic
198-
types</>. Any function declared using these types is said to be
196+
Three pseudo-types of special interest are <type>anyelement</>,
197+
<type>anyarray</>, and <type>anyenum</>,
198+
which are collectively called <firstterm>polymorphic types</>.
199+
Any function declared using these types is said to be
199200
a <firstterm>polymorphic function</>. A polymorphic function can
200201
operate on many different data types, with the specific data type(s)
201202
being determined by the data types actually passed to it in a particular
@@ -215,6 +216,9 @@
215216
<type>anyelement</type>, the actual array type in the
216217
<type>anyarray</type> positions must be an array whose elements are
217218
the same type appearing in the <type>anyelement</type> positions.
219+
<type>anyenum</> is treated exactly the same as <type>anyelement</>,
220+
but adds the additional constraint that the actual type must
221+
be an enum type.
218222
</para>
219223

220224
<para>
@@ -234,7 +238,9 @@
234238
implements subscripting as <literal>subscript(anyarray, integer)
235239
returns anyelement</>. This declaration constrains the actual first
236240
argument to be an array type, and allows the parser to infer the correct
237-
result type from the actual first argument's type.
241+
result type from the actual first argument's type. Another example
242+
is that a function declared as <literal>f(anyarray) returns anyenum</>
243+
will only accept arrays of enum types.
238244
</para>
239245
</sect2>
240246
</sect1>

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp