PostgreSQL Server Programming - Second Edition

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 you a story of how I started a simple utility view to answer the question, "What queries are running now, and which queries have been running for the longest time?" This evolved into a function based on this view, plus a few more views based on the function.

The way to get all the data to answer this question in PostgreSQL is by using the following query. Please note that the output is using an expanded mode of psql. You can turn it on using the \x meta-command:

hannu=# SELECT * FROM pg_stat_activity WHERE state='active'; -[ RECORD 1 ]----+-------------------------------- ...