Обсуждение: 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