Home I have to add a column permissions which is determined by columns roles and access. I'm trying to nest the if loops but there is error

Questions

I have to add a column permissions which is determined by columns roles and access. I'm trying to nest the if loops but there is error

byMR

August 21, 2022

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;

>Solution :

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;