Any tricks to fix this syntax error in PLPGSQL?

Question

I am creating a function to add roles. This function receives two arguments: name and a list of functions that can be executed from the role.

I am having trouble assigning the content of the v_execute argument inside the statement: GRANT EXECUTE on FUNCTION i to v_rolename;

The problem is that EXECUTE on FUNCTION expects a function name like this: fn_customer_add() and I am passing an array type variable there.

This is my plpgsql code:

    create function fn_roles_add(v_role_name varchar, v_execute varchar[]) returns character varying
        language plpgsql
    as
    $$
    DECLARE
        i varchar;
        BEGIN
            CREATE ROLE v_role_name;
            /* Function execution permissions are assigned from the array argument v_role_name */
            FOREACH i IN ARRAY v_execute
            LOOP
               GRANT EXECUTE on FUNCTION i to v_role_name;
            END LOOP;
        RETURN 'OK';
        END;
    $$;

If I replace v_execute[i] with the name of an existing function, it works correctly. But I need to be able to pass it a variable number of functions since my database is based on using functions for almost all queries.

Error:

[42601] ERROR: syntax error at or near "to"

Does anyone have any ideas to trick plpgsql in order to perform this action?

Yes, sorry they are the same, my mistake during the writing, but it does not solve it. — Gonzalo Amador Demeneghi, Mar 30 '20 at 01:56
Might want to edit the question to clear that up for others, I'm not a postgres guru by any means. — Paul T., Mar 30 '20 at 01:57

score 2 · Accepted Answer · edited Mar 31 '20 at 05:00

2

You can't use variables as identifiers (column or table names). You need dynamic SQL for this:

execute format('CREATE ROLE %I', v_role_name);

and

execute format('GRANT EXECUTE on FUNCTION %I to %I', i, v_role_name);

edited Mar 31 '20 at 05:00

Gonzalo Amador Demeneghi

13
5

answered Mar 30 '20 at 05:29

Great, but I had to replace the first type %I with %s , because when I used type %I Postgres added quotes to the string and didn't recognize the function. I was struggling with it until I realized what the problem was. If anyone knows why this happens with %I I would appreciate commenting. – Gonzalo Amador Demeneghi Mar 31 '20 at 02:53
1

@GonzaloAmadorDemeneghi: `%I` properly deals with Identifiers. I overlooked that, `i` isn't an integer, but actually a "name" – Mar 31 '20 at 05:00

Any tricks to fix this syntax error in PLPGSQL?

1 Answers1