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 ]----+--------------------------------
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 |   where state='active';

The usual process is to use a variant of the following query, which is already wrapped into a view here:

CREATE VIEW running_queries AS
SELECT
   (CURRENT_TIMESTAMP - query_start) as runtime,
   pid,
   usename,
   waiting,
   query
FROM pg_stat_activity
WHERE state='active'
ORDER BY 1 DESC
LIMIT 10;

Soon, you will notice that putting this query into a view is not enough. Sometimes, you may want to vary the number of lowest queries, while sometimes you may not want to have the full query text, but just the beginning, and so on.

If you want to vary some parameters, the logical thing to do is to use a function instead of a view, as otherwise you will need to create different views of each new requirement:

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 necessary to allow the non-superusers to at least see the type of query (SELECT, INSERT, DELETE, or UPDATE) that other users are running, but hide the exact contents. To do this, you have to make two changes to the previous function.

First, replace the row to get current_query with the following code snippet:

(CASE WHEN ( usename = session_user )
        OR (SELECT usesuper
              FROM pg_user
              WHERE usename = session_user)
      THEN
        substring(query,1,qlen)
      ELSE
        substring(ltrim(query), 1, 6) || ' ***'
      END  ) as query

This code snippet checks each row to see whether the user running the function has permission to see the full query. If the user is a superuser, then he she has permission to see the full query. If the user is a regular user, he/she will only see the full query for his/her queries. All other rows will only show the first six characters followed by *** to mark it as a shortened query string.

The other key point to allowing ordinary users to run the function, is to grant them the appropriate rights to do so. When a function is created, the default behavior is to run with the SECURITY INVOKER rights, which means that the function will be called with the rights of the user who called it. To easily grant the correct rights to call the function, the function needs to be created with the SECURITY DEFINER attribute. This causes the function to execute with the privileges of the user who created the function; therefore, creating the function as a superuser will allow it to execute as a superuser, regardless of the rights of the user who called it. This, however, should be done with caution because a superuser can be dangerous, and if you end up executing a string that is passed in, you will have all the issues of SQL injection attacks.

Now, you have a function, which you can use to get the start of the five longest-running queries using the following query:

SELECT * FROM running_queries(5,25);

In order to get a complete query, you can use the following:

SELECT * FROM running_queries(1000,1024);

You may want to define a few convenience views for the variants you use most, as follows:

CREATE OR REPLACE VIEW running_queries_tiny AS
SELECT * FROM running_queries(5,25);
CREATE VIEW running_queries_full AS
SELECT * FROM running_queries(1000,1024);

You can even redefine the original view to use the first version of the function:

CREATE OR REPLACE VIEW running_queries AS
SELECT * FROM running_queries(5,25);

This is usually not recommended, but it demonstrates the following three important things:

To resolve this, simply avoid circular references.

Even without circular references, there is still a dependency on the view called from the function. If, for instance, you need to add a column to show the application name to the running_queries view, the function needs to change as well:

CREATE OR REPLACE VIEW running_queries AS
SELECT
   CURRENT_TIMESTAMP - query_start as runtime,
   pid,
   usename,
   waiting,
   query,
   application_name as appname
FROM pg_stat_activity
ORDER BY 1 DESC
LIMIT 10;

The view definition can be changed without an error, but the next time you try to run the running_queries(int, int) function, you will get an error:

hannu=# SELECT * FROM running_queries(5,25);
ERROR:  structure of query does not match function result type
DETAIL:  Number of returned columns (5) does not match expected column count (6).
CONTEXT:  PL/pgSQL function "running_queries" line 3 at RETURN QUERY

To fix this, you need to add an additional column to the function. This is one of the dangers of reusing types in this way: you might end up breaking functions unintentionally. PostgreSQL won't tell you, when you change a type in this way, whether any functions were using this type and will only fail when you try to run the query:

CREATE OR REPLACE FUNCTION running_queries(rows int, qlen int)
  RETURNS SETOF running_queries AS
$$
BEGIN
   RETURN QUERY SELECT
      runtime,
      pid,
      usename,
      waiting,
     (CASE WHEN ( usename= session_user )
        OR (select usesuper
              from pg_user
             where usename = session_user)
      THEN
        substring(query,1,qlen)
      ELSE
        substring(ltrim(query), 1, 6) || ' ***'
      END) as query,
      appname   
    FROM running_queries
   ORDER BY 1 DESC
   LIMIT rows;
END;
$$
LANGUAGE plpgsql
SECURITY DEFINER;