Skip to main content
 首页 » 数据库

PostgreSQL 用户定义数据类型

2022年07月19日138落叶无声

PostgreSQL 用户定义数据类型

本文学习如何创建PostgreSQL 用户定义数据类型,主要使用 CREATE DOMAINCREATE TYPE 语句。除了内置的数据类型, PostgreSQL支持两种类型创建自定义数据类型。

CREATE DOMAIN 创建用户定义类型,可以包括约束,如 NOT NULL, CHECK, 等。
CREATE TYPE 创建组合类型,用于存储过程中的返回值。

1. CREATE DOMAIN

PostgreSQL中 CREATE DOMAIN 创建的数据类型,可以增加必要的约束,如 NOT NULL 和 CHECK。域有在schema范围内名称唯一。在集中管理通用约束时域很方便,举例,一些表可能有相同的列,它们都不接受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或空格。除了使用check约束,还可以定义域contact_name ,可以在多个列中重用。

下面示例通过CREATE DOMAIN 创建新的域contact_name,varchar类型,不接受null和空格:

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

下面使用和普通的数据类型一样,定义first_name 和 last_name列为contact_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 会抛出错误,因为first_name包括空格:

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命令可以查看当前库中所有定义的域。也可以通过sql查询所有特定schema的域:

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 schema中域:

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'; 

2. 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+命令。

3. 总结

本文学习两个数据类型的创建,CREATE DOMAIN 类型用于字段定义; CREATE TYPE 类型用于函数返回值。


本文参考链接:https://blog.csdn.net/neweastsun/article/details/112383758
阅读延展