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

Hi,everybody!

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;
   RETURN;

 END;
 $BODY$
 LANGUAGE 'plpgsql' VOLATILE

Олег Василенко 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

"Albe Laurenz" <laurenz.albe@wien.gv.at> writes:
>    EXECUTE 'SET SESSION ROLE "' || lower(regexp_replace(wishedrole, '"', '', 'g')) || '"';

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

quote_ident() would be a far better solution.

            regards, tom lane