Thread: How to retrieve the serial number of an INSERT?

I have the following scenario, and I'm sure many others have wished to do
something similar. I perform an insert into a table where one field is a
SERIAL. I wish to perform several other inserts (on other tables) that will
reference the first record based on its SERIAL field. In the application, I
wish to name a file based on the SERIAL (I don't wish for postgres to name
the file for me, I would just like to get at the number).

The specific table is listed below. To recap, I wish to perform an insert
and also receive the 'id' field value back. Is there any way for me to do
this, or is there a better technique for achieving what I wish to do?
Thanks!

Daniel

CREATE TABLE photos (
    id serial NOT NULL,
    parent_id integer NOT NULL,
    photographer_id integer NOT NULL,
    title character varying,
    description character varying
);

I don't have a quick answer, but found

at http://www.postgresql.org/docs/aw_pgsql_book/aw_pgsql_book.sql
something,
so try playing around with these

CREATE SEQUENCE functest_seq;

SELECT nextval('functest_seq');

SELECT nextval('functest_seq');

SELECT currval('functest_seq');

SELECT setval('functest_seq', 100);

SELECT nextval('functest_seq');

CREATE SEQUENCE customer_seq;

you could put these in a pl function, and return the value, after doing the insert?

these are different AFAIK from serial datatypes, sequences are


http://www.faqs.org/docs/ppbook/x2632.htm#USINGTHESERIALDATATYPEA


On Tue, Apr 27, 2004 at 10:09:26PM -0400, Daniel Whelan wrote:
> I have the following scenario, and I'm sure many others have wished to do
> something similar. I perform an insert into a table where one field is a
> SERIAL. I wish to perform several other inserts (on other tables) that will
> reference the first record based on its SERIAL field. In the application, I
> wish to name a file based on the SERIAL (I don't wish for postgres to name
> the file for me, I would just like to get at the number).
>
> The specific table is listed below. To recap, I wish to perform an insert
> and also receive the 'id' field value back. Is there any way for me to do
> this, or is there a better technique for achieving what I wish to do?
> Thanks!
>
> Daniel
>
> CREATE TABLE photos (
>    id serial NOT NULL,
>    parent_id integer NOT NULL,
>    photographer_id integer NOT NULL,
>    title character varying,
>    description character varying
> );
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
>               http://archives.postgresql.org

--
joe speigle
www.sirfsup.com

On Tue, Apr 27, 2004 at 22:09:26 -0400,
  Daniel Whelan <merlin@ophelan.com> wrote:
> I have the following scenario, and I'm sure many others have wished to do
> something similar. I perform an insert into a table where one field is a
> SERIAL. I wish to perform several other inserts (on other tables) that will
> reference the first record based on its SERIAL field. In the application, I
> wish to name a file based on the SERIAL (I don't wish for postgres to name
> the file for me, I would just like to get at the number).
>
> The specific table is listed below. To recap, I wish to perform an insert
> and also receive the 'id' field value back. Is there any way for me to do
> this, or is there a better technique for achieving what I wish to do?
> Thanks!

Unless you have done something odd the function currval('photos_id_seq')
will return the id value of the last record added to photos in the
current session. You can use this function directly in sql statements
so that you don't have to pass the value back to the application in many
cases.

>
> Daniel
>
> CREATE TABLE photos (
>    id serial NOT NULL,
>    parent_id integer NOT NULL,
>    photographer_id integer NOT NULL,
>    title character varying,
>    description character varying
> );