I'm trying to derive a result table from a input table and condition table in postgresql-CodePudding

This is the code but I'm getting errors while compiling : The condition table permissions can be changed if pleased that's why I'm adding the permissions column, the result table needs a permission column for all the users according to the values of condition table

-- create
CREATE TABLE EMPLOYEE (
  empId INTEGER PRIMARY KEY,
  username TEXT NOT NULL,
  userrole TEXT NOT NULL,
  roles TEXT NOT NULL,
  accesses TEXT NOT NULL
);

-- insert
INSERT INTO EMPLOYEE VALUES (0001, 'Clark','President', 'Admin','privileged');
INSERT INTO EMPLOYEE VALUES (0002, 'Dave','sales rep', 'Operational role','not privileged');
INSERT INTO EMPLOYEE VALUES (0003, 'Ava','finance manager', 'Managerial role','privileged');

-- fetch 
SELECT * FROM EMPLOYEE;

--mastertable
CREATE TABLE CONDITION (
  userrole TEXT NOT NULL,
  accesses TEXT NOT NULL,
  permissions TEXT NOT NULL
);

--insertintomastertable
INSERT INTO CONDITION VALUES ('admin','privileged','granted');
INSERT INTO CONDITION VALUES ('admin','privileged','revoked');
INSERT INTO CONDITION VALUES ('Managerial role','not privileged','granted');
INSERT INTO CONDITION VALUES ('Managerial role','privileged','revoked');
INSERT INTO CONDITION VALUES ('Operational role','not privileged','granted');
INSERT INTO CONDITION VALUES ('Operational role','privileged','revoked');


--resulttable
CREATE TABLE RESULT (
  empId INTEGER PRIMARY KEY,
  username TEXT NOT NULL,
  userrole TEXT NOT NULL,
  roles TEXT NOT NULL,
  permission TEXT
  );
  
--insertintoresult
INSERT INTO RESULT (empId, username, userrole, roles)
SELECT empId, username, userrole, roles
FROM EMPLOYEE;


DO
$do$
BEGIN
  IF (EMPLOYEE.roles and EMPLOYEE.accesses) == (CONDITION.roles and CONDITION.accesses) THEN
  
    RESULT.permission := 'GRANTED';
    WHERE (EMPLOYEE.roles and EMPLOYEE.accesses) == (CONDITION.roles and CONDITION.accesses)
      FROM EMPLOYEE
      FROM CONDITION
  ELSE 
    RESULT.permission := 'REVOKED';
  END IF;
END
$do$;

SELECT * FROM RESULT

CodePudding user response：

You can update the result table with join also, while insert i changed the query a little bit.

--insertintoresult
INSERT INTO RESULT (empId, username, userrole, roles,permission)
SELECT empId, e.username, e.userrole, roles,
c.permissions
FROM EMPLOYEE e,CONDITION c where e.roles=c.userrole and e.accesses= c.accesses order by empId;


SELECT * FROM RESULT;


INSERT INTO EMPLOYEE VALUES 
(0004, 'Dave new','sales rep', 'admin','privileged');
INSERT INTO EMPLOYEE VALUES 
(0005, 'Ava new','finance manager', 'Managerial role','not privileged');


INSERT INTO RESULT (empId, username, userrole, roles)
SELECT empId, username, userrole, roles
FROM EMPLOYEE ON CONFLICT (empId) DO NOTHING;

UPDATE 
    RESULT p
SET 
    permission = c.permissions
FROM 
    EMPLOYEE e,CONDITION c
WHERE 
   e.roles=c.userrole and e.accesses= c.accesses and p.empId=e.empid;
   
  
   SELECT * FROM RESULT order by empid;