FAQ
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
);

Search Discussions

  • Joseph speigle at Apr 28, 2004 at 2:30 am
    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
  • Bruno Wolff III at Apr 28, 2004 at 7:05 am

    On Tue, Apr 27, 2004 at 22:09:26 -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!
    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
    );

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-novice @
categoriespostgresql
postedApr 28, '04 at 2:09a
activeApr 28, '04 at 7:05a
posts3
users3
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase