Thread: Re: Restart increment to 0 each year = re-invent the se

Hi,

You can set a sequence 'nextval' with the following statement :

SELECT setval('XXX_YYY_seq',0);

XXX is the table name.
YYY is the name of the field containing the 'serial' value.

The next value inserted in the table will then have a (serial) value of '0'
or '1', I am not entirely sure which (I think '1').

Kind regards,
Alexander Priem.


-----Oorspronkelijk bericht-----
Van: Bruno Baguette [mailto:pgsql-ml@baguette.net]
Verzonden: maandag 26 april 2004 11:21
Aan: pgsql-general@postgresql.org
Onderwerp: [GENERAL] Restart increment to 0 each year = re-invent the
sequences mecanism ?

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@baguette.net

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
      subscribe-nomail command to majordomo@postgresql.org so that your
      message can get through to the mailing list cleanly

> You can set a sequence 'nextval' with the following statement :
>
> SELECT setval('XXX_YYY_seq',0);

The statement above will not work (... at least, it will not work in
PostgreSQL 7.3.1 -- I don't know if the new version has changed this
behavior...but I doubt it). You have to use something like

CREATE OR REPLACE FUNCTION public.set_sequence(name, int4)
  RETURNS int4 AS
'
DECLARE
  l_sequence_name ALIAS FOR $1;
  l_last_value ALIAS FOR $2;
BEGIN
  IF  l_last_value = 0 THEN
    PERFORM setval(l_sequence_name,1, False);
  ELSE
    PERFORM setval(l_sequence_name,l_last_value);
  END IF;
RETURN 0;
END;'
  LANGUAGE 'plpgsql' VOLATILE;


>
> XXX is the table name.
> YYY is the name of the field containing the 'serial' value.
>
> The next value inserted in the table will then have a (serial) value of
> '0' or '1', I am not entirely sure which (I think '1').
> Alexander Priem.

--Berend Tober