Using set role inside a function question

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;

Responses

Browse pgsql-novice by date

  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.