提要:本教程将向您介绍如何通过 PostgreSQL 的 CREATE DOMAIN 和 CREATE TYPE 语句创建用户自定义数据类型。

除了内置数据类型之外,PostgreSQL 还允许用户通过下面的语句自定义数据类型:

  • CREATE DOMAIN:用 NOT NULLCHECK 等约束创建用户自定义数据类型
  • CREATE TYPE:通常用于创建存储过程中作为返回数据类型的复合类型

PostgreSQL 的 CREATE DOMAIN

在 PostgreSQL 中,域(DOMAIN) 是指含有 NOT NULLCHECK 等约束的数据类型。每个域在 schema 作用域中都拥有唯一的名字。

域,用于集中管理具有常见约束条件的字段。例如,某些表可能包含需要 CHECK约束 的文本字段,以确保值不为空,也不包含空格。

下面创建一个表:

CREATE TABLE mail_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_namelast_name 不能为 NULL,并且不能包含空白字符。为了更具维护性,可以像下面这样创建一个 contact_name 域:

CREATE DOMAIN contact_name AS 
    VARCHAR NOT NULL CHECK (value !~ '\s');

然后,使用 contact_name 作为数据类型来创建表:

CREATE TABLE mail_list (
    id serial PRIMARY KEY,
    first_name contact_name,
    last_name contact_name,
    email VARCHAR NOT NULL
);

要修改删除一个域,可以使用 ALTER DOMAINDROP DOMAIN 语句。

要查看当前数据库里所有的域,可以在 psql 中使用 \dD 命令:

sjk66=#\dD
                                     List of domains
 Schema |     Name     |       Type        | Modifier |               Check
--------+--------------+-------------------+----------+-----------------------------------
 public | contact_name | character varying | not null | CHECK (VALUE::text !~ '\s'::text)
(1 row)

PostgreSQL 的 CREATE TYPE

CREATE TYPE语句允许你创建一个复合类型,它可以被用作函数的返回类型。

假设你想有一个返回多个值的函数:film_id, titlerelease_year。首先,像下例这样,创建一个 film_summary 类型:

CREATE TYPE film_summary AS (
    film_id INT,
    title VARCHAR,
    release_year YEAR
); 

接下来,将 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;

然后,调用这个函数:

SELECT
    *
FROM
    get_film_summary (40);

要修改删除一个类型,可以使用 ALTER TYPEDROP TYPE 语句。

要查看当前数据库里所有的自定义类型,可以在 psql 中使用 \dT 命令:

sjk66=# \dT
         List of data types
 Schema |     Name     | Description
--------+--------------+-------------
 public | film_summary |
(1 rows)
上一篇:PostgreSQL JSON类型
下一篇:psql命令行工具