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