Thread: SQL INSERT/TRIGGER Help
Hi all, I'm not sure how to phrase this question... I have a table that requires a foreign key of another table - this foreignkey is automatically generated and the key field in the 'foreign' table. So I have the following situation (kind of):<br/><br /> Table <u>Observation</u><blockquote><b>Time_Stamp</b> - <i>primary key</i></blockquote><blockquote>...</blockquote><blockquote>Observation_ID- <i>foreign key</i></blockquote> Table <u>Observation_Value</u><blockquote><b>Observation_ID</b>- <i>primary key</i></blockquote><blockquote>...</blockquote> Soobviously I need to insert into the table <i>Observation_Value</i> first before I can insert into table <i>Observation</i>,but how to I get the automatically generated foreign key?<br /><br /> I was thinking of using a trigger- when a value is inserted into <i>Observation_Value</i> then add the primary key to a new table (probably a view)and get that value for insertion into <i>Observation</i> then drop temporary table. I wanted to know if there's an easierway to do this? I can't change the table structure in any way either.... any help would be appreciated. I'm not evensure what type of search query I should use to find help on this topic either...<br /><br />Regards,<br />Pooven<br />
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
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:
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
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 );
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 IYou can simple use currval() for this.
> can insert into table Observation, but how to I get the automatically generated
> foreign key?
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
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
Aww man thank you so much! It worked like a charm! Have a smashing day :D<br /><br /><div class="gmail_quote">On Dec 10,2007 9:43 AM, A. Kretschmer <<a href="mailto:andreas.kretschmer@schollglas.com">andreas.kretschmer@schollglas.com </a>>wrote:<br /><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt0.8ex; padding-left: 1ex;">am Mon, dem 10.12.2007, um 9:27:58 +0200 mailte Poovendran Moodley folgendes: <br /><divclass="Ih2E3d">> I'm not really sure how to the currval() method. I've read up on it and I<br />> noticed itworks with nextval() and setval(). The parameter for currval() is a<br />> regex - is there a regex to represent themost recently automatically generated <br />> number ( i.e. a serial field)? If there isn't, I was thinking that atrigger<br />> could be used so that when an INSERT is executed against the Observation_Value<br /><br /><br /></div>Okay,i explain this a little bit more: <br /><br />first, i create two tables, master and slave. master containsa<br />serial-field, this field is referenced by the slave table:<br /><br />test=# create table master (id serialprimary key, name text);<br />NOTICE: CREATE TABLE will create implicit sequence "master_id_seq" for serial column" <a href="http://master.id" target="_blank">master.id</a>"<br />NOTICE: CREATE TABLE / PRIMARY KEY will create implicitindex "master_pkey" for table "master"<br />CREATE TABLE<br />test=*# create table slave(master_id int referencesmaster, val text); <br />CREATE TABLE<br /><br /><br />Now i have 2 tables and a sequence named 'master_id_seq',and now the<br />insert's:<br /><br /><br />test=*# insert into master (name) values ('test');<br />INSERT0 1<br />test=*# insert into slave (master_id, val) values (currval('master_id_seq'), 'value'); <br />INSERT 0 1<br/><br /><br /><br />The parameter for currval() is the name of the sequence. Note: you have to<br />insert in the mastertable first, this calls the nextval() for the<br />sequence. After, within this database session, you can use currval()to <br />obtain the actual value for this sequence. And yes, this way is safe<br />also for concurrency.<br /><divclass="Ih2E3d"><br /><br />Andreas<br />--<br />Andreas Kretschmer<br />Kontakt: Heynitz: 035242/47150, D1: 0160/7141639(mehr: -> Header) <br />GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA <a href="http://wwwkeys.de.pgp.net" target="_blank">http://wwwkeys.de.pgp.net</a><br/><br />---------------------------(end of broadcast)---------------------------<br/></div>TIP 6: explain analyze is your friend <br /></blockquote></div><br />
Poovendran Moodley escribió: > 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)? It's not a regex. I assume you are confused because it says "regclass". This is shorthand for "registered class" (where "class" is a synonymous for "relation", in this case a sequence). -- Alvaro Herrera http://www.amazon.com/gp/registry/5ZYLFMCVHXC "La felicidad no es mañana. La felicidad es ahora"