Running A FOREACH after a CTE in a PostgreSQL procedure?

Question

I have a PLPGSQL procedure that:

A) Inserts an employee into an Employee table. B) Also insert the generated serial eid into another Manager table. C) Finally, the procedure also dictates an array of course_areas that I would like to insert in a specialize table.

Procedure where C and D array are the course_areas

CALL add_employee('Athena', '22222222', 'athena@outlook.com', '2012-12-12', '111', 'instructor', 300.0, NULL, 
'{C,D}'::text[]);

Employees

CREATE TABLE Employees (
    eid SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    phone TEXT NOT NULL,
    email TEXT NOT NULL,
    join_date DATE NOT NULL,
    address TEXT NOT NULL,
    depart_date DATE
);

Managers

CREATE TABLE Managers (
    eid INT PRIMARY KEY,
    monthly_salary DECIMAL(10,2) NOT NULL,
    FOREIGN KEY (eid) REFERENCES Employees(eid)
        ON DELETE CASCADE
);

Specializes

CREATE TABLE Specializes (
    eid INT NOT NULL,
    name TEXT NOT NULL,
    PRIMARY KEY (eid, name),
    FOREIGN KEY (eid) REFERENCES Employees(eid)
        on DELETE CASCADE,
    FOREIGN KEY (name) REFERENCES Course_areas(name)
        on DELETE CASCADE
);

procedure.sql

CREATE OR REPLACE PROCEDURE add_employee (name TEXT, phone TEXT, email TEXT, joinDate DATE, address TEXT, category TEXT, monthlySalary DECIMAL(10,2) default NULL, hourlySalary DECIMAL(10,2) default NULL, courseAreas TEXT[] default NULL)
...

WITH FIRST AS (
  INSERT INTO Employees(
    name, phone, email, join_date, address
  ) 
  VALUES 
    (
      name, phone, email, joinDate, address
    ) RETURNING eid
), 
SECOND AS (
  INSERT INTO Full_time_instructors(eid, monthly_salary) 
  SELECT 
    eid, 
    monthlySalary 
  FROM 
    ROWS RETURNING eid
) 
FOREACH area IN ARRAY courseAreas 
  LOOP 
    RAISE NOTICE '%', area; -- should print "C" and "D"
  END LOOP;

Error

ERROR:  syntax error at or near "FOREACH"
LINE 27:         FOREACH area IN ARRAY courseAreas

I can get A) and B) to work.

How can I use FOREACH to iterate through my courseAreas that I pass to the procedure such that I can insert each course area and the eid into a Specialize table?

score 2 · Accepted Answer · answered Mar 29 '21 at 09:54

The FOREACH is a PLPGSQL control structure whereas the CTE is a SQL feature. You can't mix these. Instead of using CTE you can simply perform the first statement, using the RETURNING clause to retrieve the eid to a local variable. You can then use this variable with the subsequent statement and inside your FOREACH loop.

This question gives an overview of getting the new serial value using returning: Get default serial value after INSERT inside PL/pgSQL.

Running A FOREACH after a CTE in a PostgreSQL procedure?

1 Answers1