Re: Autoincremental value

From: Brendan Jurd <blakjak(at)blakjak(dot)sytes(dot)net>
To: gnari <gnari(at)simnet(dot)is>
Cc: adburne(at)asocmedrosario(dot)com(dot)ar, pgsql-general(at)postgresql(dot)org, daniel(at)manitou-mail(dot)org
Subject: Re: Autoincremental value
Date: 2004-08-14 06:56:38
Message-ID: 411DB7A6.30708@blakjak.sytes.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general


gnari wrote:

>From: <adburne(at)asocmedrosario(dot)com(dot)ar>:
>
>
>>Hi I'm a newbie in postgresql, I came from MSSQL, MySQL and now
>>I'm testing postgres.
>>In mysql there is a way to make a second autoincrement field, just:
>>
>>create table table1
>>(field1 integer,
>>field2 integer autoincrement,
>>primary key (field1,field2))
>>
>>when insert rows:
>>
>>insert into table1 (field1) values (1);
>>insert into table1 (field1) values (1);
>>insert into table1 (field1) values (2);
>>
>>and then select * from table1, you get:
>>field1| field2
>>------+-------
>> 1 | 1
>> 1 | 2
>> 2 | 1
>>------+-------
>>
>>there is a way to do this with postgres???
>>
>>
>
>it seems it is possible with triggers:
>
>create table table1 (
> field1 integer,
> field2 integer,
> primary key (field1,field2)
>);
>
>create or replace function table1_subcnt()
> returns trigger as '
> begin
> select coalesce(max(field2),0)+1 from table1
> where field1=NEW.field1
> into NEW.field2;
> return NEW;
> end;
>' language plpgsql;
>
>
>
...

Rather than using an aggregate function ( max() ) on the table, which
could be expensive over a very great number of rows, why not use a
sequence? If it's good enough for a true serial, then it should be good
enough for this value-dependant one. You'd still use the trigger, but
simplify it. Like so:

CREATE SEQUENCE table1_field2_seq;

CREATE OR REPLACE FUNCTION fill_field2() RETURNS trigger AS '
BEGIN
IF new.field2 IS NULL THEN
SELECT nextval( ''table1_field2_seq'' ) INTO new.field2
END IF;
RETURN new;
END;
' LANGUAGE 'plpgsql';

CREATE TRIGGER insert_table1 BEFORE INSERT on table1 FOR EACH ROW
EXECUTE PROCEDURE fill_field2();

This gives the same result, without the added burden of running MAX for
every insert, and because it's a sequence, the results will work even if
multiple inserts are trying to run at very similar times.

HTH

BJ

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message David Garamond 2004-08-14 09:01:39 Re: psql wishlist: value completion
Previous Message Bruce Momjian 2004-08-14 03:10:48 Re: PostgreSQL 8.0 Feature List?