pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedautomatic incrementation
3+ messages / 3 participants
[nested] [flat]
* automatic incrementation
@ 1999-06-09 08:47 Nuchanard Chiannilkulchai <nuch@valigene.com>
0 siblings, 1 reply; 3+ messages in thread
From: Nuchanard Chiannilkulchai @ 1999-06-09 08:47 UTC (permalink / raw)
To: pgsql-sql@hub.org
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: [SQL] automatic incrementation
@ 1999-06-09 13:15 José Soares <jose@sferacarta.com>
parent: Nuchanard Chiannilkulchai <nuch@valigene.com>
0 siblings, 1 reply; 3+ messages in thread
From: José Soares @ 1999-06-09 13:15 UTC (permalink / raw)
To: Nuchanard Chiannilkulchai <nuch@valigene.com>; +Cc: pgsql-sql@hub.org
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'
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: [SQL] automatic incrementation
@ 1999-06-09 15:40 Nikolay Mijaylov <nmmm@nsi.bg>
parent: José Soares <jose@sferacarta.com>
0 siblings, 0 replies; 3+ messages in thread
From: Nikolay Mijaylov @ 1999-06-09 15:40 UTC (permalink / raw)
To: pgsql-sql@hub.org
----- 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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~1999-06-09 15:40 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-06-09 08:47 automatic incrementation Nuchanard Chiannilkulchai <nuch@valigene.com>
1999-06-09 13:15 ` José Soares <jose@sferacarta.com>
1999-06-09 15:40 ` Nikolay Mijaylov <nmmm@nsi.bg>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox