Postgres Pro
Downloads
Home   >   mailing lists

Thread: automatic incrementation

automatic incrementation

From
Nuchanard Chiannilkulchai
Date:
hello,

I have a table with the field 'id' which i would like to increment at
each insert automatically.
The idea is to create a function which determines the max(id) and then a
rule to insert instead
the real insertion instruction, with the next id value.

-- table
create table toto (       id      int4       primary key,       project char(8) not null,       comments text
);
insert into toto values (1,'PTest','ndfjSHJG');
insert into toto values (2,'PTest','ndfjSHJG');
insert into toto values (3,'PTest','ndfjSHJG');

-- function
create function nextid(toto) returns int4       as ' select max($1.id)+1 as id ; '       language 'sql';

**** ERROR:  type id lookup of 12 failed
This is my first problem

-- rule
create rule r_id  as on insert to toto       do instead       insert into toto (id, project, comments) values
(nextid(),
new.project, new.comments);

**** The next problem may be the rule writing !!!
I would like to have , even some other suggestions to make it works.
I can't use a sequence table because I've already one in the database.

Thanks,   Nuch

Re: [SQL] automatic incrementation

From
José Soares
Date:

Nuchanard Chiannilkulchai ha scritto:

> hello,
>
> I have a table with the field 'id' which i would like to increment at
> each insert automatically.
> The idea is to create a function which determines the max(id) and then a
> rule to insert instead
> the real insertion instruction, with the next id value.
>
> -- table
> create table toto (
>         id      int4       primary key,
>         project char(8) not null,
>         comments text
> );
> insert into toto values (1,'PTest','ndfjSHJG');
> insert into toto values (2,'PTest','ndfjSHJG');
> insert into toto values (3,'PTest','ndfjSHJG');
>
> -- function
> create function nextid(toto) returns int4
>         as ' select max($1.id)+1 as id ; '
>         language 'sql';
>
> **** ERROR:  type id lookup of 12 failed
> This is my first problem
>
> -- rule
> create rule r_id  as on insert to toto
>         do instead
>         insert into toto (id, project, comments) values (nextid(),
> new.project, new.comments);
>
> **** The next problem may be the rule writing !!!
> I would like to have , even some other suggestions to make it works.
> I can't use a sequence table because I've already one in the database.
>
> Thanks,
>     Nuch

What about a SERIAL data type ?

create table toto (       id          SERIAL,       project  char(8) not null,       comments text);

--
______________________________________________________________
PostgreSQL 6.5.0 on i586-pc-linux-gnu, compiled by gcc 2.7.2.3
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
Jose'

Re: [SQL] automatic incrementation

From
"Nikolay Mijaylov"
Date:
----- Original Message -----
From: José Soares <jose@sferacarta.com>
To: Nuchanard Chiannilkulchai <nuch@valigene.com>
Cc: <pgsql-sql@hub.org>
Sent: 09 Þíè 1999 ã. 16:15
Subject: Re: [SQL] automatic incrementation


>
>
> Nuchanard Chiannilkulchai ha scritto:
>
> > hello,
> >
> > I have a table with the field 'id' which i would like to increment at
> > each insert automatically.
> > The idea is to create a function which determines the max(id) and then a
> > rule to insert instead
> > the real insertion instruction, with the next id value.
> >
> > -- table
> > create table toto (
> >         id      int4       primary key,
> >         project char(8) not null,
> >         comments text
> > );
> > insert into toto values (1,'PTest','ndfjSHJG');
> > insert into toto values (2,'PTest','ndfjSHJG');
> > insert into toto values (3,'PTest','ndfjSHJG');
> >
> > -- function
> > create function nextid(toto) returns int4
> >         as ' select max($1.id)+1 as id ; '
> >         language 'sql';
> >
> > **** ERROR:  type id lookup of 12 failed
> > This is my first problem
> >
> > -- rule
> > create rule r_id  as on insert to toto
> >         do instead
> >         insert into toto (id, project, comments) values (nextid(),
> > new.project, new.comments);
> >
> > **** The next problem may be the rule writing !!!
> > I would like to have , even some other suggestions to make it works.
> > I can't use a sequence table because I've already one in the database.
> >
> > Thanks,
> >     Nuch
>
> What about a SERIAL data type ?
>
> create table toto (
>         id          SERIAL,
>         project  char(8) not null,
>         comments text);
>
> --
> ______________________________________________________________
> PostgreSQL 6.5.0 on i586-pc-linux-gnu, compiled by gcc 2.7.2.3
> ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
> Jose'
>
>

What about sequence???
i use some alike...

create sequence toto_seq(.....);

create table toto (        id          integer primary key default nextval("toto_seq"),        project  char(8) not
null,       comments text
 
);

insert into toto(project, comments) values ('PTest','ndfjSHJG');

(may be there are SQL errors, but they are syntax only:))))
           Niki