函数

Tags:
Categories:

函数创建

下面是一个每天都重新开始计数的函数为例：

daily_sequences 表存储生成的序列数据。每执行一下次 `daily_sequence()`` 函数，就会写入一条数据。

摘自 stack overflow 问答 How to create sequence which start from 1 in each day。

创建 daily_sequences 表

create table daily_sequences (
  day date, s integer, primary key (day, s)
);

创建 daily_sequence 函数

create or replace function daily_sequence()
returns int as $$
  with d as (
    delete from daily_sequences where day < current_date
  )
  insert into daily_sequences (day, s)
  select current_date, coalesce(max(s), 0) + 1
  from daily_sequences
  where day = current_date
  returning s
  ;
$$ language sql;

取号

select daily_sequence();