Select nextval problem

Earlier it was suggested I do this:

SELECT nextval('my_sequence') as id

Then do the insert with the sequence and all other operations with the
"id". Sure you might have a few holes in the sequence if you abort an
insert, but this way you dont have to mess with OID's etc.

This looks like the best solution, my only problem is the SQL:

SELECT nextval('prodid_seq') as id;

INSERT INTO product VALUES (nextval('prodid_seq'),'...',...,'');

OK up to this point.

Now how do I SELECT back the record I just inserted.

SELECT * FROM product WHERE prodid = id;
ERROR: Attribute 'id' not found

OK, I'm not very good at SQL yet. How do you put the sequence number in
a place holder, then do the insert, and finally retrieve the record you
just inserted by matching the prodid with the number in the place holder.

Thanks for your help in this matter.

Mark