Re: How to execute 'set session role' from plpgsql function? - Mailing list pgsql-general

From Albe Laurenz
Subject Re: How to execute 'set session role' from plpgsql function?
Date
Msg-id D960CB61B694CF459DCFB4B0128514C20267A1FE@exadv11.host.magwien.gv.at
Whole thread Raw
In response to How to execute 'set session role' from plpgsql function?  (Олег Василенко<pulp@mail.ru>)
Responses Re: How to execute 'set session role' from plpgsql function?  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-general
Олег Василенко 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

pgsql-general by date:

Previous
From: Klint Gore
Date:
Subject: 8.3.3 win32 crashing
Next
From: Ivan Sergio Borgonovo
Date:
Subject: Re: NOT DEFERRABLE as default, why and how to manage it.