Re: SQL INSERT/TRIGGER Help

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(dot)kretschmer(at)schollglas(dot)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
>