From: Poovendran Moodley <poovenm@gmail.com>
To: A. Kretschmer <andreas.kretschmer@schollglas.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: SQL INSERT/TRIGGER Help
Date: Mon, 10 Dec 2007 09:27:58 +0200
Message-ID: <b18bbf6f0712092327v1924db67n234bcb37aab634a9@mail.gmail.com> (raw)
In-Reply-To: <20071210064425.GA917@a-kretschmer.de>
References: <b18bbf6f0712092236m48fa2e28s80b7bbb30c80dc22@mail.gmail.com>
<20071210064425.GA917@a-kretschmer.de>
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
>
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: poovenm@gmail.com, andreas.kretschmer@schollglas.com
Subject: Re: SQL INSERT/TRIGGER Help
In-Reply-To: <b18bbf6f0712092327v1924db67n234bcb37aab634a9@mail.gmail.com>
* 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