PostgreSQL 枚举(enum)类型

PostgreSQL 枚举(enum)类型

1 创建删除枚举类型

drop type if exists  attachs.efiletypes;
create type attachs.efiletypes as enum ('other', 'image', 'vedio','file');

2 枚举转换为整数,下标从1开始

drop cast if exists (attachs.efiletypes as integer);
drop function if exists enum2position(anyenum);

/****************************************************************************************
	枚举转换为integer函数,下标从1开始
  注意需要函烽必须带stable strict属性
****************************************************************************************/
create or replace function enum2position(anyenum)
	returns integer
as $$
	select array_position(enum_range($1), $1);
$$ language sql stable strict;

/****************************************************************************************
	枚举转换为integer类型函数
	int to enum select (enum_range(null::attachs.efiletypes))[1];
****************************************************************************************/
create cast (attachs.efiletypes as integer) with function enum2position(anyenum);  

使用方法

select ('image'::attachs.efiletypes)::integer

3 整数转换为枚举,下标从1开始

注意:下标不能超过枚举值的数量

select ((enum_range(null::attachs.efiletypes))[1])

4 应用举例

例如发布图文信息时,如果设计为内容和图片一对多关系的话需要先保存内容,然后才能上传图片.如果其它文章也需要该图片的话需要再上传一次,由此带来几个缺点:

• 附件需要多次存储;
• 附件管理不方便,例如上传了一些不合法的附件,查找起来困难;
• 对使用者来说非常不方便,每次引用都需要上传一次图片,虽然可以用url方式,但url不便于记忆.

为克服上述问题,可以把附件设计为windows文件管理器的模式.使用者可以在自已建立附件分类(也可以叫目录,不建使用默认分类),把需要用到的附件先上传至服务器,使用时通过选择的方式播入附件,在编辑时插入和删除附件同时记录到附件关联性表中.

下面的为附件为图片时的表结构设计:

drop table  if exists attachs.relations;
drop type if exists  attachs.texternal;
drop type if exists  attachs.trelation;
drop cast if exists (attachs.efiletypes as integer);
drop type if exists  attachs.efiletypes;

/****************************************************************************************
	附件类型枚举
****************************************************************************************/
create type attachs.efiletypes as enum ('other', 'image', 'vedio','file');

/****************************************************************************************
	附件类型枚举转换为integer类型函数
	int to enum select (enum_range(null::attachs.efiletypes))[1];
****************************************************************************************/
create cast (attachs.efiletypes as integer) with function enum2position(anyenum);

/****************************************************************************************
	附件来源类型
****************************************************************************************/
create type attachs.trelation as(
	id bigint,								--附件编号,附件来源由type指出
	type attachs.efiletypes			--1:其它\2:图\3:视频\4文件
);


/****************************************************************************************
	使用此附件的外部来源类型
****************************************************************************************/
create type attachs.texternal as(
	featureid integer,										--功能编号
	targetid bigint											--功能对应的记录编号
);

/****************************************************************************************
	附件关联性
****************************************************************************************/
create table attachs.relations(
	objectid bigserial not null,										--唯一编号
	external attachs.texternal not null,						--使用此附件的外部来源
	relation attachs.trelation not null,							--附件内部来源
	constraint pk_relations_objectid primary key(objectid) with (fillfactor=80) using index tablespace idxcore
)with (fillfactor=80,
		autovacuum_enabled=true,toast.autovacuum_enabled=true,
		autovacuum_vacuum_threshold=100,autovacuum_analyze_threshold=200,
		toast.autovacuum_vacuum_threshold=100);

create index idx_relations_external on attachs.relations(((external).featureid),((external).targetid))  with (fillfactor=80) tablespace idxcore;
create index idx_relations_relation on attachs.relations(((relation).type),((relation).id))  with (fillfactor=80) tablespace idxcore;

/****************************************************************************************
	删除外部来源时记得从附件关联性表中删除数据
drop function if exists attachs.relationRemove(integer,bigint[]);
****************************************************************************************/
create or replace function attachs.relationRemove(integer,bigint[])
	returns  void
as $$
    delete from attachs.relations where ((external).featureid)=$1 and ((external).targetid)=any($2);
$$ language sql;

drop table if exists attachs.imgdatas;
drop table if exists attachs.images;

drop cast if exists (attachs.eimgtypes as integer);
drop type if exists  attachs.eimgtypes;

/****************************************************************************************
	图片类型枚举
****************************************************************************************/
create type attachs.eimgtypes as enum ('other', 'icon', 'gif','png','jpeg');

/****************************************************************************************
	图片类型枚举转换为integer类型函数
	int to enum select (enum_range(null::attachs.eimgtypes))[1];
****************************************************************************************/
create cast (attachs.eimgtypes as integer) with function enum2position(anyenum);

/****************************************************************************************
	文件(上传的附件)扩展名和mimi类型表
	本表是由程序自动维护的,不要人工操作
****************************************************************************************/
create table attachs.images(
	objectid bigserial not null,										--唯一编号
	catalogid bigint not null,										--每个人的附件分类(也可以叫目录,不建使用默认分类),这里就不贴了,大家根据自己的需求设计
	imgtype attachs.eimgtypes not null,						--文件类型
	name text not null,												--原始文件名(不含扩展名)
	mime text not null,												--mime文件类型
	generate timestamptz not null,								--上传时间
	author attachs.tauthor not null,							--作者
	constraint pk_images_objectid primary key(objectid) with (fillfactor=80) using index tablespace idxcore,
	constraint fk_images_catalogid foreign key(catalogid)  references attachs.catalogs(objectid) on delete cascade
)with (fillfactor=80,
		autovacuum_enabled=true,toast.autovacuum_enabled=true,
		autovacuum_vacuum_threshold=100,autovacuum_analyze_threshold=200,
		toast.autovacuum_vacuum_threshold=100);

create index idx_images_range on attachs.images(to_date(generate))  with (fillfactor=80) tablespace idxcore;
create index idx_images_catalog1 on attachs.images(((author).id),((author).type))  with (fillfactor=80) tablespace idxcore where 1=catalogid;
/****************************************************************************************
	文件(上传的附件)原始数据存储表
	本表是由程序自动维护的,不要人工操作
****************************************************************************************/
create table attachs.imgdatas(
	objectid bigint not null,										--唯一编号
	data bytea not null,											--二进制原始数据,实际应用时,上传是图片应该写在磁盘上(包括原始图和缩略图),数据库中存储的用途是当磁盘上的文件被删除时自动恢复											
	constraint pk_imgdatas_objectid primary key(objectid) with (fillfactor=80) using index tablespace idxcore,
	constraint fk_imgdatas_objectid foreign key(objectid)  references attachs.images(objectid) on delete cascade
)with (fillfactor=80,
		autovacuum_enabled=true,toast.autovacuum_enabled=true,
		autovacuum_vacuum_threshold=100,autovacuum_analyze_threshold=200,
		toast.autovacuum_vacuum_threshold=100);