POSTGRESQL CTE - With prepared statement, parameter and projection mapping

postgresql-cte.sql

PREPARE emp_info AS

  WITH RECURSIVE

    subdept(id, name, parent_id) AS (
      SELECT
        emp.department_id,
        dpt.name,
        dpt.department_parent_id
      FROM department dpt
        JOIN employee emp USING (department_id)
      WHERE emp.employee_id = $1
      UNION ALL
      SELECT
        dpt.department_id,
        dpt.name,
        dpt.department_parent_id
      FROM department dpt
        JOIN subdept ON dpt.department_id = subdept.parent_id
    ),

    dept_people AS (
      SELECT
        emp.employee_id,
        emp.name,
        lead(emp.name) OVER age_wdw,
        lag(emp.name) OVER age_wdw
      FROM employee emp
        JOIN employee emp2 USING (department_id)
      WHERE emp2.employee_id = $1
      WINDOW age_wdw AS (
        ORDER BY emp.birthdate DESC )
    )

  SELECT
    emp.name,
    people.lead,
    people.lag,
    array_agg(subdept.name)
  FROM employee emp
    CROSS JOIN subdept
    JOIN dept_people people USING (employee_id)
  WHERE emp.employee_id = $1
  GROUP BY emp.name, people.lead, people.lag;

EXECUTE emp_info(49);