query_duration.sql

/*
 * Got this from Jason Boyer to report query duration, client and the query string.
 */
SELECT pid as "PID", now()-query_start AS "Duration", state AS "State",
client_addr AS "Client", application_name as "Application",
REGEXP_REPLACE(REPLACE(SUBSTRING(TRIM(LEADING ' ' FROM query) FOR 140), E'\\n', ' '), E'\\s+', ' ', 'g') AS "Query"
FROM pg_stat_activity
WHERE state NOT LIKE 'idle%'
AND query NOT LIKE '%now()-query_start%'
AND query NOT LIKE 'START_REPLICATION%'
ORDER BY query_start DESC;