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

["Albe Laurenz" <laurenz.albe@xxxxxxxxxx>]

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