PostgreSQL: How to Get 'serial' Value of the Inserted Record and Use with a Variable

• Download source - 627 B

Introduction

Consider we have the tables "Student" and "Subject" both with primary keys with serial type. We also have a relational table "StudentSubject", which references the records of the first two.

To create the initial environment, let's execute the following script:

CREATE TABLE "Student"
(
  "Id" serial NOT NULL,
  "Name" text NOT NULL,
  CONSTRAINT "Student_Pkey" PRIMARY KEY ("Id")
)

CREATE TABLE "Subject"
(
  "Id" serial NOT NULL,
  "Name" text NOT NULL,
  CONSTRAINT "Subject_Pkey" PRIMARY KEY ("Id")
)

CREATE TABLE "StudentSubject"
(
  "StudentId" integer NOT NULL,
  "SubjectId" integer NOT NULL,
  CONSTRAINT "StudentSubject_PKey" 
  PRIMARY KEY ("StudentId", "SubjectId"),
  CONSTRAINT "StudentSubject_Student_FKey" FOREIGN KEY ("StudentId")
      REFERENCES "Student" ("Id") MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT "StudentSubject_Subject_FKey" FOREIGN KEY ("SubjectId")
      REFERENCES "Subject" ("Id") MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)

We have to insert a record in each of "Student" and "Subject" tables as well as relate them with each other by inserting another record in "StudentSubject" table.

As PostgreSQL does not support to have variables without PL/PgSQL, we need to create a PL/PgSQL block and perform the insertion in that. For this instance, we are using an anonymous block, but it can be done with functions or stored procedures as well.

DO $$
DECLARE StudentId integer;
DECLARE SubjectId integer;
BEGIN
  INSERT INTO "Subject"(
            "Name")
          VALUES ('Geography')
          RETURNING "Id" INTO SubjectId;

  INSERT INTO "Student"(
            "Name")
          VALUES ('John Smith')
          RETURNING "Id" INTO StudentId;

  INSERT INTO "StudentSubject"(
            "StudentId", "SubjectId")
          VALUES (StudentId, SubjectId);
END $$