Re: Restart increment to 0 each year = re-invent the sequences mecanism ?

You don't have to mess with sequences.

If there are two fields ID and year then the next number is:

next_number := ( select ID from table_name where year = year_from_current_date
order by ID desc limit 1 ) + 1;

Regards,
Clodoaldo

--- Bruno Baguette <pgsql-ml(at)baguette(dot)net> escreveu: > Hello,
>
> I have to design a table wich will store some action reports. Each
> report have an ID like this 1/2004, 2/2004, ... and each years, they
> restart to 1 (1/2004, 1/2005, 1/2006,...).
>
> So, I was thinking to split that in two fields : one with the increment
> and one with the year. But I don't know how can I manage the sequences
> since I have to restart to 0 each year...
>
> Do you think I should re-invent the sequences mecanism with a second
> table and a stored procedure, only to manage theses 'home-made' sequences ?
>
> Or should I create some sequences like myseq_2004, myseq_2004,
> my_seq_2005,... and use a concatenation of the myseq_ string and the
> current year when calling nextval and curvall ?
>
> Or is there another way to do that ?
>
> Thanks in advance :-)
>
> --
> Bruno Baguette - pgsql-ml(at)baguette(dot)net
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly

______________________________________________________________________

Yahoo! Messenger - Fale com seus amigos online. Instale agora!
http://br.download.yahoo.com/messenger/