九月 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
语句创建用户定义类型。