How to create sequence which start from 1 in each day

Sequence should return values 1,2,3 etc starting for 1 for every day. current_date should used for day determination.

For example, calling today first time it shoudl return 1, in second time 2 etc.

Tomorrow, first call shoud return again 1, second call 2 etc.

Postgres 9.1 is used.

Use a table to keep the sequence:

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

This function will retrieve the next value:

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

select daily_sequence();

Be prepared to retry in case of an improbable duplicate key value error. If previous days' sequences are not necessary delete them to keep the table and the index as light as possible:

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