Re: [Spam] Re: Restart increment to 0 each year = re-in - Mailing list pgsql-general

From Priem, Alexander
Subject Re: [Spam] Re: Restart increment to 0 each year = re-in
Date
Msg-id 2A07EC2D0BC2774AAD6F74769F60D52A083315@ahmose.cict_ad.nl
Whole thread Raw
List pgsql-general
> > 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


I use a statement like "SELECT setval('XXX_YYY_seq',0)" myself. It
definitely works in 7.4(+).


> 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



pgsql-general by date:

Previous
From:
Date:
Subject: Re: Restart increment to 0 each year = re-invent the se
Next
From: Clodoaldo Pinto Neto
Date:
Subject: Re: Restart increment to 0 each year = re-invent the sequences mecanism ?