I have to add a column permissions which is determined by columns roles and access. I'm trying-CodePudding

The code i'm using is like this
CREATE TABLE EMPLOYEE ( empId INTEGER PRIMARY KEY, username TEXT NOT NULL, userrole TEXT NOT NULL, roles TEXT NOT NULL, accesses TEXT NOT NULL );

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');

SELECT * FROM EMPLOYEE;

ALTER TABLE EMPLOYEE
ADD COLUMN permissions VARCHAR;

DO
$do$
BEGIN
IF EMPLOYEE.roles='Admin' THEN
  IF EMPLOYEE.accesses='privileged' THEN
    SET permissions = 'GRANTED';
      
else
  IF EMPLOYEE.roles='Operational role' THEN
    IF EMPLOYEE.accesses='not privileged' THEN
      SET permissions = 'GRANTED';
      
else
  IF EMPLOYEE.roles='Managerial role' THEN
    IF EMPLOYEE.accesses='not privileged' THEN
      SET permissions = 'GRANTED';
      
  else
  SET permissions = 'REVOKED';
END IF;
END
$do$;

SELECT * FROM EMPLOYEE;

CodePudding user response：

Do you need in this:

SELECT *,
       CASE WHEN (roles, accesses) IN ( ('Admin','privileged'),
                                        ('Operational role','not privileged'),
                                        ('Managerial role','not privileged') )
            THEN 'GRANTED'
            ELSE 'REVOKED'
            END AS permissions 
FROM employee;