Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 92D812E2F08 for ; Mon, 10 Dec 2007 05:13:54 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 59666-03 for ; Mon, 10 Dec 2007 05:13:51 -0400 (AST) Received: from rv-out-0910.google.com (rv-out-0910.google.com [209.85.198.185]) by postgresql.org (Postfix) with ESMTP id BDEEE2E0040 for ; Mon, 10 Dec 2007 05:13:50 -0400 (AST) Received: by rv-out-0910.google.com with SMTP id l15so1705345rvb for ; Mon, 10 Dec 2007 01:13:49 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; bh=076G5usAjVcXn8LvjxtizG0shxCLQbUhR036fn6VSj0=; b=BeGsHrZCDjeYkn1z3CNBX72hSGfqcNVXwXnwgW5Z9GzyPnjSX9+W5Lbi6xn++j02tFugKWMmT8SWKGSKJdeqDtoC1LKtWolgivmFr7kNCF+0ERiRAOayO516qYQaKO5J8uObXMwxp5CX/6ep1M7PNR2bJ5GfjCW20lIkw3gHuME= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=NGJwxCpTvQBHplTAcVNsND9TdBcm7Zwai6fifG9So1IK8iDRzbpUKiSF5PqXIFhO74X7Ywsgq10owUt0QoXYx5IjrWpBABOppKD1W+Is5z44DVPSRyZ6mM8w33jMh/VVQ65nQwDAJ7l2SHzDvkT8Ky/Xf6YqaH2W7+ASGl8xq9c= Received: by 10.140.180.42 with SMTP id c42mr4074040rvf.1197278029378; Mon, 10 Dec 2007 01:13:49 -0800 (PST) Received: by 10.140.128.1 with HTTP; Mon, 10 Dec 2007 01:13:49 -0800 (PST) Message-ID: Date: Mon, 10 Dec 2007 11:13:49 +0200 From: "Poovendran Moodley" To: "A. Kretschmer" Subject: Re: SQL INSERT/TRIGGER Help Cc: pgsql-sql@postgresql.org In-Reply-To: <20071210074347.GB917@a-kretschmer.de> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_11094_26309743.1197278029392" References: <20071210064425.GA917@a-kretschmer.de> <20071210074347.GB917@a-kretschmer.de> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200712/34 X-Sequence-Number: 29726 ------=_Part_11094_26309743.1197278029392 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline Aww man thank you so much! It worked like a charm! Have a smashing day :D On Dec 10, 2007 9:43 AM, A. Kretschmer wrote: > 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 > > ---------------------------(end of broadcast)--------------------------- > TIP 6: explain analyze is your friend > ------=_Part_11094_26309743.1197278029392 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline Aww man thank you so much! It worked like a charm! Have a smashing day :D

On Dec 10, 2007 9:43 AM, A. Kretschmer <andreas.kretschmer@schollglas.com > wrote:
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

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

------=_Part_11094_26309743.1197278029392--