Database Reference
In-Depth Information
CREATE VIEW running_queries AS
SELECT
CURRENT_TIMESTAMP - query_start as runtime,
pid,
usename,
waiting,
query
FROM pg_stat_activity
ORDER BY 1 DESC
LIMIT 10;
But soon you will notice, that putting this query into a view is not enough. Sometimes
you want to vary the number of lowest queries, sometimes you don't want to have
the full query text, but just the beginning, and so on.
If you want to vary some parameters, the logical thing is to use a function instead of
a view, as follows:
CREATE OR REPLACE FUNCTION running_queries(rows
int, qlen int)
RETURNS SETOF running_queries AS
$$
BEGIN
RETURN QUERY SELECT
runtime,
pid,
usename,
waiting,
substring(query,1,qlen) as query
FROM running_queries
ORDER BY 1 DESC
LIMIT rows;
END;
$$ LANGUAGE plpgsql;
As a security precaution, the default behavior of the
pg_stat_activity
view is
that only superusers can see what other users are running. Sometimes it may be
