九月 16, 2023
摘要：在本教程中，您将学习如何在 PostgreSQL 中使用CREATE DOMAIN和CREATE TYPE语句创建用户定义数据类型。
除了内置数据类型之外，PostgreSQL 还允许您通过以下语句创建用户定义的数据类型：
CREATE DOMAIN创建具有NOT NULL、CHECK等约束的用户定义数据类型。CREATE TYPE创建在存储过程中使用的复合类型，它可以作为返回值的数据类型。
PostgreSQL CREATE DOMAIN 语句
在 PostgreSQL 中，域是一种具有可选约束的数据类型，例如NOT NULL和CHECK。域在模式范围内具有唯一的名称。
域对于集中管理具有共同约束的字段非常有用。例如，某些表可能同样都具有不接受 NULL 和空格的列。
以下语句创建一个表，名为mailing_list：
CREATE TABLE mailing_list (
id SERIAL PRIMARY KEY,
first_name VARCHAR NOT NULL,
last_name VARCHAR NOT NULL,
email VARCHAR NOT NULL,
CHECK (
first_name !~ '\s'
AND last_name !~ '\s'
)
);
在此表中，first_name和last_name列均不接受 NULL 和空格。您可以创建一个contact_name域并在多个列中重用它，而不是定义CHECK约束。
以下语句使用CREATE DOMAIN创建一个名为contact_name的新域，它使用VARCHAR数据类型并且不接受 NULL 和空格：
CREATE DOMAIN contact_name AS
VARCHAR NOT NULL CHECK (value !~ '\s');
您可以将contact_name像常规内置类型一样，用作first_name和last_name列的数据类型：
CREATE TABLE mailing_list (
id serial PRIMARY KEY,
first_name contact_name,
last_name contact_name,
email VARCHAR NOT NULL
);
以下语句将新行插入mailing_list表中：
INSERT INTO mailing_list (first_name, last_name, email)
VALUES('Jame V','Doe','jame.doe@example.com');
PostgreSQL 发出以下错误，因为名字包含空格：
ERROR: value for domain contact_name violates check constraint "contact_name_check"
以下语句有效，因为它不违反contact_name类型的任何约束：
INSERT INTO mailing_list (first_name, last_name, email)
VALUES('Jane','Doe','jane.doe@example.com');
要更改或删除域，请分别使用ALTER DOMAIN或DROP DOMAIN。
要查看当前数据库中的所有域，可以使用\dD命令，如下：
test=#\dD
List of domains
Schema | Name | Type | Modifier | Check
--------+--------------+-------------------+----------+-----------------------------------
public | contact_name | character varying | not null | CHECK (VALUE::text !~ '\s'::text)
(1 row)
获取域类型信息
要获取特定模式中的所有域，请使用以下查询：
SELECT typname
FROM pg_catalog.pg_type
JOIN pg_catalog.pg_namespace
ON pg_namespace.oid = pg_type.typnamespace
WHERE
typtype = 'd' and nspname = '<schema_name>';
以下语句返回当前数据库public模式中的域：
SELECT typname
FROM pg_catalog.pg_type
JOIN pg_catalog.pg_namespace
ON pg_namespace.oid = pg_type.typnamespace
WHERE
typtype = 'd' and nspname = 'public';
PostgreSQL CREATE TYPE
CREATE TYPE语句允许您创建一个复合类型，它可以用作函数的返回类型。
假设您想要一个函数能返回多个值：film_id、title和release_year。第一步是创建一个类型，比如叫film_summary，如下所示：
CREATE TYPE film_summary AS (
film_id INT,
title VARCHAR,
release_year SMALLINT
);
其次，使用film_summary数据类型作为函数的返回类型：
CREATE OR REPLACE FUNCTION get_film_summary (f_id INT)
RETURNS film_summary AS
$$
SELECT
film_id,
title,
release_year
FROM
film
WHERE
film_id = f_id ;
$$
LANGUAGE SQL;
第三步，调用get_film_summary()函数：
SELECT * FROM get_film_summary(40);
要更改用户定义类型，请使用ALTER TYPE语句。要删除用户定义类型，请使用DROP TYPE语句。
如果您使用psql程序，您可以使用\dT或\dT+命令列出当前数据库中的所有用户定义类型：
dvdrental=# \dT
List of data types
Schema | Name | Description
--------+--------------+-------------
public | contact_name |
public | film_summary |
public | mpaa_rating |
(3 rows)
在本教程中，您学习了如何在 PostgreSQL 中使用CREATE DOMAIN和CREATE TYPE语句创建用户定义类型。