ddl check mode
RekGRpth opened this issue · 17 comments
How about ddl check mode? I.e. at create or replace function?
What do you think?
I want to check function at create or replace (instead start).
CREATE OR REPLACE FUNCTION public.check_on_create_fx_trg_func()
RETURNS event_trigger
LANGUAGE plpgsql
AS $function$
declare
r record;
rc int;
begin
for r in select * from pg_event_trigger_ddl_commands()
loop
if r.classid = 'pg_proc'::regclass and
exists(select * from pg_proc
where oid = r.objid
and prolang = (select oid from pg_language where lanname = 'plpgsql'))
then
perform * from plpgsql_check_function_tb(r.objid, fatal_errors=>false);
get diagnostics rc = row_count;
if (rc > 0) then
raise warning 'there are some errors or warnings (%) detected by plpgsql_check on function %', rc, r.objid::regprocedure;
end if;
end if;
end loop;
end;
$function$
;
create event trigger plpgsql_check_evtr on ddl_command_end execute function check_on_create_fx_trg_func();
If is this possible to do via shared_preload_libraries for every databases without creating extension in it?
you can install this to template1, and then every new database will have this functionality.
There is an possibility to use executor hooks, and then it can be started by implicitly via shared_preload_libraries. But this is relatively aggressive technique. Why you need it?
There is an possibility to use executor hooks, and then it can be started by implicitly via shared_preload_libraries. But this is relatively aggressive technique. Why you need it?
It is simpler to use for many existing databases.
CREATE OR REPLACE FUNCTION public.check_on_create_fx_trg_func()
RETURNS event_trigger
LANGUAGE plpgsql
AS$function$
declare
r record;
rc int;
begin
for r in select * from pg_event_trigger_ddl_commands()
loop
if r.classid = 'pg_proc'::regclass and
exists(select * from pg_proc
where oid = r.objid
and prolang = (select oid from pg_language where lanname = 'plpgsql'))
then
perform * from plpgsql_check_function_tb(r.objid, fatal_errors=>false);
get diagnostics rc = row_count;
if (rc > 0) then
raise warning 'there are some errors or warnings (%) detected by plpgsql_check on function %', rc, r.objid::regprocedure;
end if;
end if;
end loop;
end;
$function$
;
How about to place this function into extension to use it so
create event trigger plpgsql_check_evtr on ddl_command_end execute function plpgsql_check_on_create_fx_trg_func();I have no idea about parameters for plpgsql_check_function_tb
May be like TG_ARGV?
I have no idea about parameters for plpgsql_check_function_tb
May be it is possible to realize it like every start (or fresh) ?
Every start (or fresh) mode, as I understand, does not require additional named arguments?
sure - almost all arguments have default values. But you should specify an argument if you want to check performance, and if you want to check trigger or polymorphic arguments. You can use something like global session variables, but it can be a big trap for a lot of users.
I want specify arguments via ... postgresql.conf file! Because they will same for all databases.