Thread: Need help with currval and nextvall...
Considering the function below, is it possible to get the value of the album_id from the first INSERT statement and use it in the 2nd INSERT statement as I have tried to do here? Or do has the sequence not update until the end of the LOOP? I have tried this and get: ERROR: Attribute 'album_album_id_seq' not found How would I accomplish this? CREATE FUNCTION catalog_batch() RETURNS text AS ' DECLARE myrec RECORD; BEGIN FOR myrec IN SELECT * FROM catalog LOOP INSERT INTO album (columns,....) VALUES ('matching values',....); INSERT INTO track (columns,....) VALUES ( currval(album_album_id_seq), 'other matching values',.... ); <rest of code> END LOOP; RETURN 'Batch Complete'; END; ' LANGUAGE 'plpgsql'; Thanks, Dan Jewett
You need to quote (single quotes) the name of the sequence... and you need to use nextval('album_album_id_seq') before calling currval(). At least I think so. On Fri, 22 Nov 2002, Dan Jewett wrote: > Considering the function below, is it possible to get the value of > the album_id from the first INSERT statement and use it in the 2nd > INSERT statement as I have tried to do here? Or do has the sequence > not update until the end of the LOOP? > > I have tried this and get: ERROR: Attribute 'album_album_id_seq' not found > > How would I accomplish this? > > CREATE FUNCTION catalog_batch() RETURNS text AS ' > > DECLARE > myrec RECORD; > > BEGIN > FOR myrec IN SELECT * FROM catalog LOOP > > INSERT INTO album (columns,....) VALUES ('matching values',....); > > INSERT INTO track (columns,....) VALUES ( > currval(album_album_id_seq), 'other matching values',.... ); > > <rest of code> > > END LOOP; > RETURN 'Batch Complete'; > END; > > ' LANGUAGE 'plpgsql'; > > Thanks, > Dan Jewett > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) >
>You need to quote (single quotes) the name of the sequence... and you need >to use nextval('album_album_id_seq') before calling currval(). > >At least I think so. Thanks Phillip, I know I'm supposed to have the quotes but I keep getting : PostgreSQL said: ERROR: parser: parse error at or near "album_album_id_seq" unless I take them out. I also tried nextval() with the same error returned: ERROR: Attribute 'album_album_id_seq' not found. Dunnoh, Dan
> >You need to quote (single quotes) the name of the sequence... and you need > >to use nextval('album_album_id_seq') before calling currval(). > > > >At least I think so. > > Thanks Phillip, > > I know I'm supposed to have the quotes but I keep getting : > PostgreSQL said: ERROR: parser: parse error at or near > "album_album_id_seq" unless I take them out. > > I also tried nextval() with the same error returned: ERROR: Attribute > 'album_album_id_seq' not found. Huh... well, what happens if you just run: SELECT NEXTVAL('album_album_id_seq'); directly in psql? -philip
Dan, > I know I'm supposed to have the quotes but I keep getting : > PostgreSQL said: ERROR: parser: parse error at or near > "album_album_id_seq" unless I take them out. > > I also tried nextval() with the same error returned: ERROR: Attribute > 'album_album_id_seq' not found. Because you're "nesting" quotes in a function, you'll need to double them: SELECT CURRVAL(''album_album_id_seq'') And you should *not* call Nextval; the INSERT is doing that already because of the SERIAL type on your table. -Josh Berkus