postgresql存储过程

通过指定字符串和长度，返回该字符串在数据库的存储次数，可用来统计字符出现的次数，或生成自增序列。

1. 创建存储过程

--
-- Name: gb_code_sequence(character varying, integer); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION gb_code_sequence(in_object_type character varying, in_code_length integer, OUT out_sequence character varying) RETURNS character varying
    LANGUAGE plpgsql
    AS $$
  declare
     v_object_type varchar(30);
     v_sequence integer;
  begin
    v_object_type := trim(in_object_type);
  if exists (select 1 from gb_code_sequence where type = v_object_type) then
      update gb_code_sequence set sequence_number = sequence_number + 1 where type = v_object_type;
  else
       begin
      insert into gb_code_sequence(type, sequence_number) values (v_object_type, 1);
       exception when others then
      update gb_code_sequence set sequence_number = sequence_number + 1 where type = v_object_type;
       end;
  end if;
  select sequence_number into v_sequence from gb_code_sequence where type = v_object_type;
  out_sequence:= lpad(v_sequence::varchar,in_code_length,'0');
 -- commit;
  end;
$$;


ALTER FUNCTION public.gb_code_sequence(in_object_type character varying, in_code_length integer, OUT out_sequence character varying) OWNER TO postgres;

2. 建存储过程对应的表

--
-- Name: gb_code_sequence; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE gb_code_sequence (
    type character varying(255) DEFAULT NULL::character varying,
    sequence_number bigint
);


ALTER TABLE gb_code_sequence OWNER TO postgres;