Re: 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(dot)albe(at)wien(dot)gv(dot)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