Database Reference
In-Depth Information
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:47.912253-04
waiting | f
state | active
query | select * from
pg_stat_activity;
The usual process is to use a variant of the following query, here already wrapped
into a view:
