From: | Garry Chen <gc92(at)cornell(dot)edu> |
---|---|
To: | "pgsql-novice(at)postgresql(dot)org" <pgsql-novice(at)postgresql(dot)org> |
Subject: | Using set role inside a function question |
Date: | 2017-11-07 19:42:41 |
Message-ID: | FBAAE934-3C4A-4E01-9FE9-3BCD4402332A@cornell.edu |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
Hi All,
I have a function pass in a user login id (p_runas_id) and the it will insert a row into a table after that set role to that pass in id. This function complied fine but getting ERROR: role "p_runas_id" does not exist CONTEXT: SQL statement "SET ROLE p_runas_id" when execute. Any suggestion?
Thank you very much,
Garry
CREATE OR REPLACE FUNCTION set_netid_context(p_runas_id varchar(30))
RETURNS void
AS $$
Declare
v_dm_name varchar(30);
v_runid varchar(30);
v_ora_id numeric;
BEGIN
INSERT INTO obiee_context_audit(context_date, ora_sid, db_user, runas_id)
VALUES (now(), pg_backend_pid(), SESSION_USER, p_runas_id);
SET ROLE p_runas_id;
END; $$
LANGUAGE plpgsql;
From | Date | Subject | |
---|---|---|---|
Next Message | Vianello, Daniel A | 2017-11-07 19:57:04 | Re: Using set role inside a function question |
Previous Message | Stephen Froehlich | 2017-11-04 18:41:49 | Re: Old solutions for listing tables by tablespace broken in PG 10 for partitions. |