Functions based on views

Creating a function based on a view definition is a very powerful and flexible way of providing information to users. As an example of this, I will tell a story of how I started a simple utility view for answering the question, "What queries are running now and which ones have been running the longest time?" It evolved into a function based on this view plus a few more views based on the function.

The way to get all data to answer this question in PostgreSQL is by using the following query:

hannu=# select * from pg_stat_activity;

-[ RECORD 1 ]----+--------------------------------
datid            | 17557
datname          | hannu
pid              | 8933
usesysid         | 10
usename          | postgres
application_name | psql
client_addr      | 
client_hostname  | 
client_port      | -1
backend_start    | 2013-03-19 13:47:45.920902-04
xact_start       | 2013-03-19 14:05:47.91225-04
query_start      | 2013-03-19 14:05:47.91225-04
state_change     | 2013-03-19 14:05...