postgres: Using trigger function to remove user from a group-CodePudding

I have a trigger function that gets a person's username from their first and last name, and then drops the user with that username from a group with privileges, essentially locking that person out of the database.

returns "trigger" AS'
   DECLARE
   uname varchar(255);
   
begin
    uname = (SELECT CONCAT(LOWER(first_name), LOWER(last_name)) FROM members WHERE library_card_id = (SELECT members_library_card_id FROM borrowed_books WHERE fine_id IS NOT NULL));
    alter group members drop user uname;
end;
' LANGUAGE 'plpgsql';


CREATE TRIGGER block_member_if_fine
    AFTER UPDATE on borrowed_books
    FOR EACH ROW
    EXECUTE PROCEDURE f_block_member_if_fine();

However, when I use the trigger function, I get an error role "uname" does not exist Is there a way to use uname as a variable? Or maybe there's a different way of dropping a user with a trigger function?

CodePudding user response：

Try this :

CREATE OR REPLACE PROCEDURE f_block_member_if_fine()
RETURNS "trigger" LANGUAGE 'plpgsql' AS
$$
DECLARE
   uname varchar(255); 
BEGIN
    SELECT CONCAT(LOWER(m.first_name), LOWER(m.last_name))
      INTO uname 
      FROM members AS m
     WHERE m.library_card_id = NEW.members_library_card_id
       AND NEW.fine_id IS NOT NULL;

    IF FOUND
    THEN
        EXECUTE 'ALTER GROUP members DROP USER '|| quote_nullable(uname) ;
    END IF ;
    RETURN NEW ;
END ;
$$

CREATE TRIGGER block_member_if_fine
    AFTER UPDATE on borrowed_books
    FOR EACH ROW
    EXECUTE PROCEDURE f_block_member_if_fine();