Hi PostgreSQL Community, Recently I was reading about functions Immutability and security definer but got confused Whether the below two functions can be marked immutable or not 1. If a function has constant Raise notice inside it. Eg.
CREATE OR REPLACE FUNCTION text_equals(text, text) RETURNS boolean AS $$BEGIN RAISE NOTICE 'Comparing two texts'; RETURN $1 = $2;END; $$ LANGUAGE plpgsql; 2. If a function has Raise notice but extracting current user inside notice, although its output purely depends on its input arguments eg. CREATE OR REPLACE FUNCTION text_equals(text, text) RETURNS boolean AS $$BEGIN RAISE NOTICE 'Current user: %', current_user; RETURN $1 = $2;END; $$ LANGUAGE plpgsql; On security definer part I am confused with the below example set role postgres; CREATE OR REPLACE FUNCTION outer_function() RETURNS TEXT AS $$ DECLARE user_text TEXT; BEGIN SELECT 'OuterFunction() -> Current user is ' || current_user INTO user_text; user_text := user_text || ' | ' || inner_function(); RETURN user_text; END; $$ LANGUAGE plpgsql VOLATILE SECURITY DEFINER; create role test; create role alex; grant create on schema public to test; set role test; CREATE OR REPLACE FUNCTION inner_function() RETURNS TEXT AS $$ DECLARE current_user_text TEXT; BEGIN current_user_text := 'InnerFunction() -> Current user is ' || current_user; RETURN current_user_text; END; $$ LANGUAGE plpgsql VOLATILE SECURITY INVOKER; set role alex; select outer_function(); outer_function ------------------------------------------------------------------------------------------- OuterFunction() -> Current user is postgres | InnerFunction() -> Current user is postgres Shouldn't it be "InnerFunction() -> Current user is alex" instead of postgres as alex called the security invoker function I tried reading docs but couldn't get any satisfactory answers, it will be helpful if someone helped me out here Thanks, Ayush Vatsa SDE AWS