W5uSFnBi

1create table daily_sequence (
2    day date, s integer, primary key (day, s)
3); 
4     
5create or replace function daily_sequence()
6returns int as $$
7    insert into daily_sequence (day, s)
8    select current_date, coalesce(max(s), 0) + 1
9    from daily_sequence
10    where day = current_date
11    returning s
12    ;
13$$ language sql;
14
15select daily_sequence(); 
16     
17create or replace function daily_sequence()
18returns int as $$
19    with d as (
20        delete from daily_sequence
21        where day < current_date
22    )
23    insert into daily_sequence (day, s)
24    select current_date, coalesce(max(s), 0) + 1
25    from daily_sequence
26    where day = current_date
27    returning s
28    ;
29$$ language sql; 
30     
31# It will run your command at 00:00 every day
32# min hour wday month mday command-to-run
33    0    0    *     *    * psql --host host.domain.com --port 32098 --db_name databaseName < my.sql 
34     
35-- We will use anonymous block here because it is impossible to use
36-- variables and functions in DDL directly 
37do language plpgsql $$
38begin
39  execute 'create sequence my_seq_day start with ' || (current_date - '1900-01-01')::varchar;
40end; $$;
41
42-- Initialize sequence
43select nextval('my_seq_day');
44
45create sequence my_seq;
46
47create or replace function nextval_daily(in p_seq varchar) returns bigint as $$
48declare
49  dd bigint;
50  lv bigint;
51begin
52  select current_date - '1900-01-01'::date into dd;
53  -- Here we should to retrieve current value from sequence
54  -- properties instead of currval function to make it session-independent 
55  execute 'select last_value from '||p_seq||'_day' into lv;
56  if dd - lv > 0 then
57    -- If next day has come
58    -- Reset main sequens
59    execute 'alter sequence '||p_seq||' restart';
60    -- And set the day sequence to the current day
61    execute 'alter sequence '||p_seq||'_day restart with '||dd::varchar;
62    execute 'select nextval('''||p_seq||'_day'')' into lv;
63  end if;
64  return nextval(p_seq);
65end; $$ language plpgsql; 
66     
67SELECT
68CASE WHEN NOT EXISTS (
69SELECT   primary_key   FROM    schema.table   WHERE   date(updated_datetime) = #{systemDate} limit 1)
70THEN 
71setval('scheam.job_seq', 1) 
72ELSE 
73nextval('scheam.job_seq') 
74END 
75     
76GRANT UPDATE ON ALL SEQUENCES IN SCHEMA ur_schema TO user;