Most common security definer functions in GPDB
1. CREATE OR REPLACE FUNCTION public.sdf_pg_stat_activity()
RETURNS SETOF pg_stat_activity AS
$BODY$
DECLARE
r RECORD ;
BEGIN
FOR r IN SELECT * FROM pg_stat_activity LOOP
RETURN NEXT r ;
END LOOP ;
RETURN ;
END ;
$BODY$
LANGUAGE plpgsql VOLATILE SECURITY DEFINER;
2. CREATE OR REPLACE FUNCTION public.sdf_pg_cancel_backend(v_pid integer)
RETURNS boolean AS
$BODY$
DECLARE
b boolean;
a text;
BEGIN
SELECT usename from pg_stat_activity where procpid=v_pid INTO a;
if a<>session_user then
RAISE NOTICE 'Process user is % but the current user is %, cannot cancel.', a,session_user;
return 0;
else SELECT pg_cancel_backend(v_pid) INTO b;
RETURN b;
END IF;
END;
$BODY$
LANGUAGE plpgsql VOLATILE SECURITY DEFINER;
3. CREATE OR REPLACE FUNCTION public.analyze_table(tname text)
RETURNS void AS
$BODY$
BEGIN
EXECUTE 'ANALYZE ' || tname || ';' ;
EXCEPTION
WHEN undefined_table THEN
RAISE EXCEPTION 'table "%" does not exist', tname;
END;
$BODY$
LANGUAGE plpgsql VOLATILE SECURITY DEFINER;
4. CREATE OR REPLACE FUNCTION public.truncate_table(tname name)
RETURNS void AS
$BODY$
BEGIN
EXECUTE 'TRUNCATE TABLE '|| tname ||';';
EXCEPTION
WHEN undefined_table THEN
RAISE EXCEPTION 'table "%" does not exist', tname;
END;
5. CREATE OR REPLACE FUNCTION public.reorg_table(tname name)
RETURNS void AS
$BODY$
BEGIN
EXECUTE 'alter table ' || tname || ' set with (reorganize=true);' ;
EXCEPTION
WHEN undefined_table THEN
RAISE EXCEPTION 'table "%" does not exist', tname;
END;
$BODY$
LANGUAGE plpgsql VOLATILE SECURITY DEFINER;