0

I have a function written in plpgsql that checks if user have permissions:


CREATE OR REPLACE FUNCTION check_permission(acc_id integer, permissions text[]) RETURNS boolean AS $$
        BEGIN
          SELECT DISTINCT p.name FROM person_role pr
          JOIN role_permission rp ON pr.role_id = rp.role_id
          JOIN permission p ON rp.permission_id = p.permission_id
          WHERE account_id = acc_id ;
          
          -- check
        END;
$$ LANGUAGE plpgsql;

How can i check that select includes all permissions from permissions text[] argument?

Improve this question

1 Answer 1

Reset to default
2

You can unnest the array into a result set and from it subtract the role names from your result:

CREATE OR REPLACE FUNCTION check_permission(acc_id integer, permissions text[]) RETURNS boolean AS $$
  SELECT NOT EXISTS (
      SELECT unnest(permissions)
    EXCEPT
      SELECT DISTINCT p.name
      FROM person_role pr
      JOIN role_permission rp ON pr.role_id = rp.role_id
      JOIN permission p ON rp.permission_id = p.permission_id
      WHERE pr.account_id = acc_id
  );
$$ LANGUAGE sql;

It might however better express the intent to use a boolean aggregate over the permission table and a subquery:

SELECT bool_and(EXISTS(
  SELECT *
  FROM person_role pr
  JOIN role_permission rp ON pr.role_id = rp.role_id
  WHERE rp.permission_id = p.permission_id
    AND pr.account_id = acc_id
))
FROM permission p
WHERE p.name = ANY permissions -- or JOIN (SELECT unnest(permissions) AS name) USING (name)

Notice this will return NULL instead of TRUE when the permissions array is empty.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.