在potgresql数据库中实现类似java枚举类型的效果

前言

一般而言，实际开发中会有这样的枚举数据：

package net.w2p.Base.dict;
import net.w2p.Shared.common.EnumItemValuePair;
import java.util.ArrayList;
public enum MemberStatus {
  SUBMIT_DATA ( "提交资料",-5 )
 ,  DELETE ( "删除",-1 )
 ,  UNPASS ( "未通过",-2 )
 ,  NORMAL ( "正常",1 )
 ,  UNVERIFY ( "未审核",-3 )
 ,  FORBIDDEN ( "禁止使用",-99 )
;
private String _name;
private Integer _id;
private MemberStatus (String name, Integer id) {
this._name = name;
this._id = id;
}

public String getName() {
return this._name;
}

public Integer getId() {
return this._id;
}

/**
* 获取【MemberStatus】信息
* @param id 代号
* @return
*/
public static  MemberStatus getEnumById(Integer id)  {
 MemberStatus target = null;
 MemberStatus[] values= MemberStatus . values();
for( MemberStatus cmd : values) {
if(cmd.getId() == id) {
target = cmd;
break;
}
}
if(target == null) {
return null;
}
return target;
}

/**
* 获取【MemberStatus】信息
* @param id 代号
* @return
*/
public static  MemberStatus getEnumByName(String name)  {
MemberStatus target = null;
MemberStatus[] values= MemberStatus . values();
for( MemberStatus cmd : values) {
if(cmd.getName().equals(name)) {
target = cmd;
break;
}
}
if(target == null) {
return null;
}
return target;
}

public static String  getNameById(Integer id)  {
 MemberStatus target = null;
if(id==null){
return "";
}
for( MemberStatus cmd :  MemberStatus. values()) {
if(cmd.getId().equals(id)) {
target = cmd;
break;
}
}
if(target == null) {
return "";
}
return target.getName();
}

/**
* 获得列表信息。
* @param firm
* @return
* @throws FirmNotFoundException
*/
public static ArrayList<EnumItemValuePair> getList()  {
ArrayList<EnumItemValuePair> list=new ArrayList<EnumItemValuePair>();
for( MemberStatus cmd :  MemberStatus . values()) {
EnumItemValuePair kv=new EnumItemValuePair();
kv.setKey(cmd.getId());
kv.setValue(cmd.getName());
list.add(kv);
}
return list;
}
}

那么，在pg里面怎么才能对应java的业务代码也用上这种枚举对象呢？
下面是其中一个解决方案：

create type "EnumItem" as (
  code int,
  name varchar
  );

create type "MemberStatus" as (
  NORMAL "EnumItem",
  FORBIDDEN "EnumItem"
  );



create or replace function "getEnumItem"(
para_code int,
para_name varchar
)
returns "EnumItem"
language plpgsql
as $$
declare _enum_item "EnumItem";
begin
  _enum_item.code=para_code;
  _enum_item.name:=para_name;

    return _enum_item;

end $$;

create or replace function "getCodeFromEnumItem"(
para_enum_item "EnumItem"
)
returns int
language plpgsql
as $$

begin

    return para_enum_item.code;

end $$;

create or replace function "getNameFromEnumItem"(
para_enum_item "EnumItem"
)
returns varchar
language plpgsql
as $$

begin

    return para_enum_item.name;

end $$;



create or replace function "dict4MemberStatus"(
)
returns "MemberStatus"
language plpgsql
as $$
declare result "MemberStatus";
declare _normal_enum_item "EnumItem";
begin
  _normal_enum_item.code=1;
  _normal_enum_item.name:='正常状态';

  result.normal:="getEnumItem"(1,'正常状态');
  result.forbidden:="getEnumItem"(-1,'禁止使用');

  return result;

end $$;



create or replace function "test_001"(
)
returns varchar
language plpgsql
as $$
declare _member_status "MemberStatus";
  declare _demo_enum_item "EnumItem";
begin
  _member_status:="dict4MemberStatus"();
  _demo_enum_item:=_member_status.normal;
    raise notice '%',_demo_enum_item.name;
    raise notice '%',"getNameFromEnumItem"(_member_status.normal);
--   return _member_status.NORMAL.name;


  _demo_enum_item:="getEnumItem"(1,'demo');

  raise notice '%',_demo_enum_item.name;

  return '';

end $$;