Re: SQL INSERT/TRIGGER Help - Mailing list pgsql-sql

From Poovendran Moodley
Subject Re: SQL INSERT/TRIGGER Help
Date
Msg-id b18bbf6f0712092327v1924db67n234bcb37aab634a9@mail.gmail.com
Whole thread Raw
In response to Re: SQL INSERT/TRIGGER Help  ("A. Kretschmer" <andreas.kretschmer@schollglas.com>)
Responses Re: SQL INSERT/TRIGGER Help
Re: SQL INSERT/TRIGGER Help
List pgsql-sql
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

pgsql-sql by date:

Previous
From: "A. Kretschmer"
Date:
Subject: Re: SQL INSERT/TRIGGER Help
Next
From: "A. Kretschmer"
Date:
Subject: Re: SQL INSERT/TRIGGER Help