Thread: custom serial number

custom serial number

From
mahmoud ewiwi
Date:
hi gurus
i have a problem in generating serial number with the form yyyymm0000 how can i do that?

Mahmoud Al-Ewiwi

Al-Mahawer
Hebron- Palestine


     


Re: custom serial number

From
"A. Kretschmer"
Date:
am  Tue, dem 18.11.2008, um  1:09:44 -0800 mailte mahmoud ewiwi folgendes:
> hi gurus
> i have a problem in generating serial number with the form yyyymm0000 how can i do that?

test=# create temporary sequence foo;
CREATE SEQUENCE
test=# select to_char(current_date,
'yyyymm')||trim(to_char(nextval('foo'),'09999')); ?column?
-------------20081100001
(1 row)

test=# select to_char(current_date,
'yyyymm')||trim(to_char(nextval('foo'),'09999')); ?column?
-------------20081100002
(1 row)

test=# select to_char(current_date,
'yyyymm')||trim(to_char(nextval('foo'),'09999')); ?column?
-------------20081100003
(1 row)


HTH, Andreas
-- 
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net


Re: custom serial number

From
"Pavel Stehule"
Date:
Hello

what do you wont to do exactly?

you can try -

create sequence s;
postgres=# create sequence s;
CREATE SEQUENCE
postgres=# select to_char(current_date, 'yyyymmdd') ||
trim(to_char(nextval('s'), '0000'));  ?column?
--------------200811180002
(1 row)

postgres=# select to_char(current_date, 'yyyymmdd') ||
trim(to_char(nextval('s'), '0000'));  ?column?
--------------200811180003
(1 row)

regards
Pavel Stehule

2008/11/18 mahmoud ewiwi <mahmoud_ewiwi@yahoo.com>:
> hi gurus
> i have a problem in generating serial number with the form yyyymm0000 how can i do that?
>
> Mahmoud Al-Ewiwi
>
> Al-Mahawer
> Hebron- Palestine
>
>
>
>
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>


Re: custom serial number

From
"A. Kretschmer"
Date:
am  Tue, dem 18.11.2008, um 10:37:23 +0100 mailte Pavel Stehule folgendes:
> Hello
> 
> what do you wont to do exactly?
> 
> you can try -
> 
> create sequence s;
> postgres=# create sequence s;
> CREATE SEQUENCE
> postgres=# select to_char(current_date, 'yyyymmdd') ||
> trim(to_char(nextval('s'), '0000'));
>    ?column?
> --------------
>  200811180002
> (1 row)

What's wrong with your sequence, it starts with 2?


SCNR *g*

Andreas
-- 
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net


Re: custom serial number

From
"Pavel Stehule"
Date:
Hello

2008/11/18 A. Kretschmer <andreas.kretschmer@schollglas.com>:
> am  Tue, dem 18.11.2008, um 10:37:23 +0100 mailte Pavel Stehule folgendes:
>> Hello
>>
>> what do you wont to do exactly?
>>
>> you can try -
>>
>> create sequence s;
>> postgres=# create sequence s;
>> CREATE SEQUENCE
>> postgres=# select to_char(current_date, 'yyyymmdd') ||
>> trim(to_char(nextval('s'), '0000'));
>>    ?column?
>> --------------
>>  200811180002
>> (1 row)
>
> What's wrong with your sequence, it starts with 2?
>

missing lines :)

>
> SCNR *g*
>
> Andreas
> --
> Andreas Kretschmer
> Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
> GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>


Re: custom serial number

From
Gerardo Herzig
Date:
A. Kretschmer wrote:
> am  Tue, dem 18.11.2008, um  1:09:44 -0800 mailte mahmoud ewiwi folgendes:
>> hi gurus
>> i have a problem in generating serial number with the form yyyymm0000 how can i do that?
> 
> test=# create temporary sequence foo;
> CREATE SEQUENCE
> test=# select to_char(current_date,
> 'yyyymm')||trim(to_char(nextval('foo'),'09999'));
>   ?column?
> -------------
>  20081100001
> (1 row)
> 
Thats nice. You should now take care about changing months.
I guess a cron job can do the job restarting the sequence the first day
of the month. (If we are talking about Unix/Linux, off course)

Gerardo


Re: custom serial number

From
"A. Kretschmer"
Date:
am  Tue, dem 18.11.2008, um 20:56:07 -0800 mailte mahmoud ewiwi folgendes:
> Thank you hery much, it works fine with me , but how can i restart the sequence at the start of each month, or should
ido it programatically?
 

Please answer to the list and not to me.

You can write a function for that. First, check if is a new month and
create a new sequence or reset the sequence. 


Andreas
-- 
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net


Re: custom serial number

From
mahmoud ewiwi
Date:
Thank you very much, it works fine with me , but how can i restart the sequence at the start of each month, or should i
doit programatically?
 

Mahmoud Al-Ewiwi

Al-Mahawer
Hebron- Palestine



--- On Wed, 11/19/08, A. Kretschmer <andreas.kretschmer@schollglas.com> wrote:

> From: A. Kretschmer <andreas.kretschmer@schollglas.com>
> Subject: Re: [SQL] custom serial number
> To: "mahmoud ewiwi" <mahmoud_ewiwi@yahoo.com>, pgsql-sql@postgresql.org
> Date: Wednesday, November 19, 2008, 9:45 AM
> am  Tue, dem 18.11.2008, um 20:56:07 -0800 mailte mahmoud
> ewiwi folgendes:
> > Thank you hery much, it works fine with me , but how
> can i restart the sequence at the start of each month, or
> should i do it programatically?
> 
> Please answer to the list and not to me.
> 
> You can write a function for that. First, check if is a new
> month and
> create a new sequence or reset the sequence. 
> 
> 
> Andreas
> -- 
> Andreas Kretschmer
> Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr:
> -> Header)
> GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA  
> http://wwwkeys.de.pgp.net

     


Re: custom serial number

From
"A. Kretschmer"
Date:
am  Wed, dem 19.11.2008, um  0:52:07 -0800 mailte mahmoud ewiwi folgendes:
> Thank you very much, it works fine with me , but how can i restart the sequence at the start of each month, or should
ido it programatically?
 

For instance, check if a record for the actual month are in the table.
If not, use setval() to reset the sequence to 1.

http://www.postgresql.org/docs/8.3/interactive/functions-sequence.html


Andreas
-- 
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net