Postgres Pro
Downloads
Home   >   mailing lists

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

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

From
Oleg Vasylenko
Date:
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

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

From
"Dmitry Koterov"
Date:
Just a suggestion:

EXECUTE 'SET SESSION ROLE wishedrole';

won't help?


2008/8/17 Oleg Vasylenko <pulp@mail.ru>
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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general