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 exampleset 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 postgresShouldn't it be "InnerFunction() -> Current user is alex" instead of postgres as alex called the security invoker functionI tried reading docs but couldn't get any satisfactory answers, it will be helpful if someone helped me out hereThanks,Ayush VatsaSDE AWS