Thread: How to auto-increment?

Andre Lopes wrote:
> Hi,
>
> I have a table like this:
>
> id_product
> id_increment
>
> and I need to increment values in id_increment like this
>
> prod_1
> 1
>
> prod_1
> 2
>
> prod_1
> 3
>
> prod_2
> 1
>
> Wich is the best way to do this? Using a trigger? Where can I find
> examples of plpgsql doing this?

offhand, I'd have another table that has (id_product primary key,
next_increment integer), and use it to populate your id_increment
fields, bumping the next_increment each time you fetch it.

I assume its OK if there are missing increment values, like if you
delete a product/increment from your  table, or if in the middle of
inserting a new one, there's a transaction rollback for some reason?

In response to Andre Lopes :
> Hi,
>  
> I have a table like this:
>  
> id_product
> id_increment
>  
> and I need to increment values in id_increment like this
>  
> prod_1
> 1
>  
> prod_1
> 2
>  
> prod_1
> 3
>  
> prod_2
> 1
>  

Ahh, you want to count per group, yes? Do you have 8.4? If yes, i would
suggest you an other way: use a CTE-query to count that. Or create a view
based on a CTE-query.

In your case:

select id_product, row_number() over (partition by id_product) from table



> Wich is the best way to do this? Using a trigger? Where can I find examples of
> plpgsql doing this?

With a TRIGGER there are possible, but what happens if you delete the
first record containing 'prod_1'?


> Sorry for my bad english.

Mee too ...


Andreas
--
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431  2EB0 389D 1DC2 3172 0C99

In response to Andre Lopes :
> Hi,

Pleaase answer to the list and not to me, okay?


>
> This is an exemple in ORACLE of what I need. I will see if this works in
> Postgres.

Why not? You have to rewrite it for PostgreSQL, but the way is okay.

>
> Another question. It is possible in Postgres to use more than one Trigger by
> table in Postgres?

Sure, why not? This fire in alphabetical order.


Andreas
--
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431  2EB0 389D 1DC2 3172 0C99