O'Reilly logo

PostgreSQL Server Programming by Jim Mlodgenski, Kirk Roybal, Hannu Krosing

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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 | ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required