Use variable as role name in function postgresql

Question

Can't use variable as role name

I tried to create function with trigger on insert, witch perform some grant statements for given id's. I declared some variable with id value and have to use it as role name.

CREATE OR REPLACE FUNCTION permissions() RETURNS trigger
    LANGUAGE plpgsql
    AS $permissions$
    DECLARE
        id varchar  := NEW."id"; --this variable will contain user name ex. "valid.user"
    BEGIN
        GRANT USAGE ON SCHEMA zakupy TO id;
        RETURN NEW;
    END;
$permissions$;

CREATE TRIGGER permissions_insert AFTER INSERT ON public.permissions FOR EACH ROW EXECUTE PROCEDURE permissions();

When I add new row i have error message ERROR: role "id" does not exist. If I replace id to "valid.user":

GRANT USAGE ON SCHEMA zakupy TO "valid.user"

statement works perfectly.

Rémy Baron · Accepted Answer · 2019-05-22 09:14:11Z

1

try this :

CREATE OR REPLACE FUNCTION permissions() RETURNS trigger
LANGUAGE plpgsql
AS $permissions$
DECLARE
    id varchar  := NEW."id"; --this variable will contain user name ex. "valid.user"
BEGIN
    execute 'GRANT USAGE ON SCHEMA zakupy TO "'||id||'"';
    RETURN NEW;
END;
$permissions$;

answered May 22, 2019 at 9:14

Rémy Baron

1,4099 silver badges15 bronze badges

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

1 Comment

Tomasz Konopiński Over a year ago

Works perfektly. Thank you.

Collectives™ on Stack Overflow

Use variable as role name in function postgresql

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related