Re: How to execute 'set session role' from plpgsql function?

Олег Василенко wrote:
> I wish to have a function with code above, but compiller generate
> syntactic error at the line "SET SESSION ROLE wishedrole;".
>
> How to pass the wishedrole value to the structure?
>
> CREATE OR REPLACE FUNCTION f_switch_role(INOUT text,INOUT boolean) AS
> $BODY$
> DECLARE
> wishedrole ALIAS FOR $1;
> resetrole ALIAS FOR $2;
> BEGIN
> if resetrole=true then
> RESET ROLE;
> RETURN;
> end if;
>
> >>>>ERROR OCURS AT THE NEXT LINE <<<<<<
> SET SESSION ROLE wishedrole;

You need to use dynamic SQL, e.g.

EXECUTE 'SET SESSION ROLE "' || lower(regexp_replace(wishedrole, '"', '', 'g')) || '"';

> RETURN;
>
> END;
> $BODY$
> LANGUAGE 'plpgsql' VOLATILE

The "lower" and "regexp_replace" are there to prevent SQL injection.

Yours,
Laurenz Albe