Обсуждение: Triggering a table id from a sequence

My desire is to have a table auto update an "id" sequence on every
insert, relieving the front end program of the responsibility.

I've never written a trigger. I imagine that there may be a contrib
trove of them.

There may also be an easier way to approach the problem. Please feel
free to refer me to books or web sites.

TIA,

--
Lan Barnes                    lan@falleagle.net
Linux Guy, SCM Specialist     858-354-0616
Tcl/Tk Enthusiast

I don't believe that you need a trigger to do this.  When you create a table that will use an
"id"
sequence, you would simply define its datatype as a serial or bigserial.  In the background, it
will automatically create a sequence for you and it will automatical set you id column's default
valve to "nextval("your_new_sequence").  when you insert rows into your new table, simply allow
your table to use its default value to give you the next value in you sequence.

see http://www.postgresql.org/docs/8.1/interactive/datatype.html#DATATYPE-SERIAL for an example.


Regards,

Richard

--- Lan Barnes <lan@falleagle.net> wrote:

> My desire is to have a table auto update an "id" sequence on every
> insert, relieving the front end program of the responsibility.
>
> I've never written a trigger. I imagine that there may be a contrib
> trove of them.
>
> There may also be an easier way to approach the problem. Please feel
> free to refer me to books or web sites.
>
> TIA,
>
> --
> Lan Barnes                    lan@falleagle.net
> Linux Guy, SCM Specialist     858-354-0616
> Tcl/Tk Enthusiast
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
>        choose an index scan if your joining column's datatypes do not
>        match
>

>

am  10.04.2006, um 16:53:27 -0700 mailte Lan Barnes folgendes:
> My desire is to have a table auto update an "id" sequence on every
> insert, relieving the front end program of the responsibility.
>
> I've never written a trigger. I imagine that there may be a contrib
> trove of them.
>
> There may also be an easier way to approach the problem. Please feel
> free to refer me to books or web sites.

A simple way for this is a RULE. (on insert to <table> do also)

test=# create table foo (id int);
CREATE TABLE
test=# create sequence foo_seq;
CREATE SEQUENCE
test=# create rule foo_rule as on insert to foo do also select nextval('foo_seq');
CREATE RULE
test=# select * from foo_seq ;
 sequence_name | last_value | increment_by |      max_value      | min_value | cache_value | log_cnt | is_cycled |
is_called

---------------+------------+--------------+---------------------+-----------+-------------+---------+-----------+-----------
 foo_seq       |          1 |            1 | 9223372036854775807 |         1 |           1 |       1 | f         | f
(1 row)

test=# insert into foo values (5);
 nextval
---------
       1
(1 row)

test=# insert into foo values (4);
 nextval
---------
       2
(1 row)

test=# insert into foo values (3);
 nextval
---------
       3
(1 row)

test=# select last_value from foo_seq ;
 last_value
------------
          3
(1 row)


http://www.postgresql.org/docs/8.1/interactive/rules-update.html


HTH, Andreas
--
Andreas Kretschmer    (Kontakt: siehe Header)
Heynitz:  035242/47215,      D1: 0160/7141639
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net
 ===    Schollglas Unternehmensgruppe    ===