From: A. Kretschmer <andreas.kretschmer@schollglas.com>
To: pgsql-sql@postgresql.org
Subject: Re: SQL INSERT/TRIGGER Help
Date: Mon, 10 Dec 2007 08:43:47 +0100
Message-ID: <20071210074347.GB917@a-kretschmer.de> (raw)
In-Reply-To: <b18bbf6f0712092327v1924db67n234bcb37aab634a9@mail.gmail.com>
References: <b18bbf6f0712092236m48fa2e28s80b7bbb30c80dc22@mail.gmail.com>
<20071210064425.GA917@a-kretschmer.de>
<b18bbf6f0712092327v1924db67n234bcb37aab634a9@mail.gmail.com>
am Mon, dem 10.12.2007, um 9:27:58 +0200 mailte Poovendran Moodley folgendes:
> I'm not really sure how to the currval() method. I've read up on it and I
> noticed it works with nextval() and setval(). The parameter for currval() is a
> regex - is there a regex to represent the most recently automatically generated
> number ( i.e. a serial field)? If there isn't, I was thinking that a trigger
> could be used so that when an INSERT is executed against the Observation_Value
Okay, i explain this a little bit more:
first, i create two tables, master and slave. master contains a
serial-field, this field is referenced by the slave table:
test=# create table master (id serial primary key, name text);
NOTICE: CREATE TABLE will create implicit sequence "master_id_seq" for serial column "master.id"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "master_pkey" for table "master"
CREATE TABLE
test=*# create table slave(master_id int references master, val text);
CREATE TABLE
Now i have 2 tables and a sequence named 'master_id_seq', and now the
insert's:
test=*# insert into master (name) values ('test');
INSERT 0 1
test=*# insert into slave (master_id, val) values (currval('master_id_seq'), 'value');
INSERT 0 1
The parameter for currval() is the name of the sequence. Note: you have to
insert in the master table first, this calls the nextval() for the
sequence. After, within this database session, you can use currval() to
obtain the actual value for this sequence. And yes, this way is safe
also for concurrency.
Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: andreas.kretschmer@schollglas.com
Subject: Re: SQL INSERT/TRIGGER Help
In-Reply-To: <20071210074347.GB917@a-kretschmer.de>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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