Re: Autoincremental value

      adburne@asocmedrosario.com.ar writes

> 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 looks like a weird feature. Anyway to achieve the same result with
postgresql, a trigger can compute a value for field2 when needed. I believe
this would do:

CREATE OR REPLACE FUNCTION fill_field2() RETURNS trigger AS '
BEGIN
   IF new.field2 IS NULL THEN
     SELECT 1+coalesce(max(field2),0) INTO new.field2 FROM table1
        WHERE field1=new.field1;
   END IF;
   RETURN new;
END;
' LANGUAGE 'plpgsql';

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

--
 Daniel
 PostgreSQL-powered mail user agent and storage: http://www.manitou-mail.org