citus update view on distributed table

gistfile1.txt

-- Create Employees table
CREATE TABLE Employees (
    EmployeeID SERIAL PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    Department VARCHAR(50),
    Salary NUMERIC(10, 2)
);

CREATE TABLE Employees2 (
    EmployeeID SERIAL PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    Department VARCHAR(50),
    Salary NUMERIC(10, 2)
);

-- Insert some sample data
INSERT INTO Employees (FirstName, LastName, Department, Salary)
VALUES
    ('John', 'Doe', 'HR', 50000.00),
    ('Jane', 'Smith', 'IT', 60000.00),
    ('Bob', 'Johnson', 'Finance', 75000.00);

-- Create an updatable view on Employees
CREATE VIEW EmployeeView AS
SELECT * FROM Employees;

-- Create an INSTEAD OF INSERT trigger
CREATE OR REPLACE FUNCTION instead_of_insert_function()
RETURNS TRIGGER AS $$
BEGIN
    if not exists (select 1 from Employees where EmployeeID = NEW.EmployeeID) then
        INSERT INTO Employees (FirstName, LastName, Department, Salary)
        VALUES (NEW.FirstName, NEW.LastName, NEW.Department, NEW.Salary);
        INSERT INTO Employees2 (FirstName, LastName, Department, Salary)
        VALUES (NEW.FirstName, NEW.LastName, NEW.Department, NEW.Salary);
    elsif not exists (select 1 from Employees2 where EmployeeID = NEW.EmployeeID) then
        INSERT INTO Employees2 (FirstName, LastName, Department, Salary)
        VALUES (NEW.FirstName, NEW.LastName, NEW.Department, NEW.Salary);
    end if;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER instead_of_insert_trigger
INSTEAD OF INSERT ON EmployeeView
FOR EACH ROW
EXECUTE FUNCTION instead_of_insert_function();

-- Create an INSTEAD OF UPDATE trigger
CREATE OR REPLACE FUNCTION instead_of_update_function()
RETURNS TRIGGER AS $$
BEGIN
    UPDATE Employees
    SET
        FirstName = NEW.FirstName,
        LastName = NEW.LastName,
        Department = NEW.Department,
        Salary = NEW.Salary
    WHERE EmployeeID = NEW.EmployeeID;
    UPDATE Employees2
    SET
        FirstName = NEW.FirstName,
        LastName = NEW.LastName,
        Department = NEW.Department,
        Salary = NEW.Salary
    WHERE EmployeeID = NEW.EmployeeID;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

$$ LANGUAGE plpgsql;

CREATE TRIGGER instead_of_update_trigger
INSTEAD OF UPDATE ON EmployeeView
FOR EACH ROW
EXECUTE FUNCTION instead_of_update_function();

-- Create an INSTEAD OF DELETE trigger
CREATE OR REPLACE FUNCTION instead_of_delete_function()
RETURNS TRIGGER AS $$
BEGIN
    DELETE FROM Employees WHERE EmployeeID = OLD.EmployeeID;
    DELETE FROM Employees2 WHERE EmployeeID = OLD.EmployeeID;
    RETURN OLD;
END;
$$ LANGUAGE plpgsql;


CREATE TRIGGER instead_of_delete_trigger
INSTEAD OF DELETE ON EmployeeView
FOR EACH ROW
EXECUTE FUNCTION instead_of_delete_function();

select create_distributed_table('employees', 'employeeid');
select create_distributed_table('employees2', 'employeeid');


itusdb=# INSERT INTO employeeview (FirstName, LastName, Department, Salary)
VALUES
    ('John', 'Doe', 'HR', 50000.00),
    ('Jane', 'Smith', 'IT', 60000.00),
    ('Bob', 'Johnson', 'Finance', 75000.00);
INSERT 0 3
citusdb=# table employees;
 employeeid | firstname | lastname | department |  salary
------------+-----------+----------+------------+----------
         10 | John      | Doe      | HR         | 50000.00
         11 | Jane      | Smith    | IT         | 60000.00
         12 | Bob       | Johnson  | Finance    | 75000.00
(3 rows)

citusdb=# table employees2;
 employeeid | firstname | lastname | department |  salary
------------+-----------+----------+------------+----------
          8 | Jane      | Smith    | IT         | 60000.00
          7 | John      | Doe      | HR         | 50000.00
          9 | Bob       | Johnson  | Finance    | 75000.00
(3 rows)