Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 92A3B2E003C for ; Mon, 10 Dec 2007 03:33:52 -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 38018-01 for ; Mon, 10 Dec 2007 03:28:00 -0400 (AST) Received: from rv-out-0910.google.com (rv-out-0910.google.com [209.85.198.189]) by postgresql.org (Postfix) with ESMTP id 3D6682E003F for ; Mon, 10 Dec 2007 03:28:00 -0400 (AST) Received: by rv-out-0910.google.com with SMTP id l15so1675541rvb for ; Sun, 09 Dec 2007 23:27:58 -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=WRf8D1NwzSvCWPA5qj+VHGlLdhLi/h32R13GI9/sewk=; b=JqvP2KplVSEjuXWD8hfckNIyPx6rQ0/OWStiqfhmjQCEAwrguYrrg1aCUuosahWKfAXLWcMIsKPc4ryIx8tG2FJPsvkt8y2afqmWQciFT481MGpEzi/WPkGs1z70L2RwqdNdpJ0djKZj2qN14QVLEjPFWVWRH2qeERAGkQm7svc= 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=Sh74HqGOpKp094rfv4suniZZfdVbHhNPD7ebZ0i1BNpa0daxcTu3VsDkAqiXfIolBCfa825P8a5kZJUaTbwOro3e5yKshMm0Np391lZNPJAiBnXe3FbR0yw2dlj49AX9g3PlKBWp49e1KQpq6AGCDFuYf7bAeD0ekHe7oi28XTI= Received: by 10.140.88.11 with SMTP id l11mr4043683rvb.1197271678928; Sun, 09 Dec 2007 23:27:58 -0800 (PST) Received: by 10.140.128.1 with HTTP; Sun, 9 Dec 2007 23:27:58 -0800 (PST) Message-ID: Date: Mon, 10 Dec 2007 09:27:58 +0200 From: "Poovendran Moodley" To: "A. Kretschmer" Subject: Re: SQL INSERT/TRIGGER Help Cc: pgsql-sql@postgresql.org In-Reply-To: <20071210064425.GA917@a-kretschmer.de> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_10921_27891394.1197271678903" References: <20071210064425.GA917@a-kretschmer.de> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200712/32 X-Sequence-Number: 29724 ------=_Part_10921_27891394.1197271678903 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline 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* table then I'll use *setval()* to store the automatically generated field. However I'm having some trouble defining a *TRIGGER *in PostGres. I noticed that one can implement a C code to achieve the effect of a trigger, however, would normal SQL work as well? I have the following: CREATE FUNCTION doInsert(id int) AS 'SELECT setval('observation_id', new.observation_id)'; CREATE TRIGGER onObservationEntry AFTER INSERT ON Observation_Key FOR EACH STATEMENT EXECUTE PROCEDURE doInsert(new.observation_id ); Which doesn't work. I get the following error: ERROR: syntax error at or near "observation_id". I know that usually new represents, in this case, the inserted tuple, however, new doesn't seem to work with PostGres; what is the correct way to do this? I used a function because it appears that this is the only way to define a trigger. If I can simply execute the SQL statement in my function that would be awesome - but what is the syntax for this? Well I'm not really sure if I've defined my function correctly - I just imitated an example I've seen. Thanks for your help Andreas, one step closer :) *currval()* is so much better than creating a view. Regards, Pooven On Dec 10, 2007 8:44 AM, A. Kretschmer < andreas.kretschmer@schollglas.com> wrote: > am Mon, dem 10.12.2007, um 8:36:44 +0200 mailte Poovendran Moodley > folgendes: > > So obviously I need to insert into the table Observation_Value first > before I > > can insert into table Observation, but how to I get the automatically > generated > > foreign key? > > You can simple use currval() for this. > > > 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 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 > ------=_Part_10921_27891394.1197271678903 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline 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 table then I'll use setval() to store the automatically generated field. However I'm having some trouble defining a TRIGGER in PostGres. I noticed that one can implement a C code to achieve the effect of a trigger, however, would normal SQL work as well? I have the following:

CREATE FUNCTION doInsert(id int)
AS 'SELECT setval('observation_id', new.observation_id)';

CREATE TRIGGER onObservationEntry
AFTER INSERT ON Observation_Key
FOR EACH STATEMENT
EXECUTE PROCEDURE doInsert(new.observation_id );

Which doesn't work. I get the following error: ERROR: syntax error at or near "observation_id". I know that usually new represents, in this case, the inserted tuple, however, new doesn't seem to work with PostGres; what is the correct way to do this? I used a function because it appears that this is the only way to define a trigger. If I can simply execute the SQL statement in my function that would be awesome - but what is the syntax for this? Well I'm not really sure if I've defined my function correctly - I just imitated an example I've seen.

Thanks for your help Andreas, one step closer :) currval() is so much better than creating a view.

Regards,
Pooven

On Dec 10, 2007 8:44 AM, A. Kretschmer < andreas.kretschmer@schollglas.com> wrote:
am  Mon, dem 10.12.2007, um  8:36:44 +0200 mailte Poovendran Moodley folgendes:
> So obviously I need to insert into the table Observation_Value first before I
> can insert into table Observation, but how to I get the automatically generated
> foreign key?

You can simple use currval() for this.


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 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

------=_Part_10921_27891394.1197271678903--