Олег Василенко 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
