SET client_min_messages TO NOTICE;
do $$
declare
version_check boolean;
r1 record;
outputinfo varchar(80) := 'N';
waiting_threshold interval := '10 min';
begin
SELECT substring((string_to_array(version(), ' '))[2] for 3) > '9.1' into version_check;
if version_check
then
for r1 in SELECT date_trunc('second',current_timestamp - query_start) AS runtime,
datname, usename,substring(query for 40) query,waiting,pid
FROM pg_stat_activity
WHERE state = 'active'
AND current_timestamp - query_start >= waiting_threshold
and query !~* '(vacuum|analyze)'
ORDER BY 1 DESC limit 1
loop
outputinfo := '';
outputinfo := outputinfo || cast(r1.pid as varchar) ||'/'||cast(r1.runtime as varchar)||'/'||r1.query ;
end loop;
else
for r1 in SELECT date_trunc('second',current_timestamp - query_start) AS runtime,
datname, usename, substring(current_query for 40) query,waiting,procpid
FROM pg_stat_activity
where current_timestamp - query_start >= waiting_threshold
and current_query !~* '(vacuum|analyze|idle)'
ORDER BY 1 DESC limit 1
loop
outputinfo := '';
outputinfo := outputinfo || cast(r1.procpid as varchar) ||'/'||cast(r1.runtime as varchar)||'/'||r1.query ;
end loop;
end if;
raise notice '%', outputinfo;
end$$;