Обсуждение: 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 ===