Re: Query_time SQL as a function w/o creating a new type

[Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>]

"A. Kretschmer" <andreas(dot)kretschmer(at)schollglas(dot)com> writes:
> Change the SELECT procpid, ... to
>            SELECT into procpid, ...

For something like this, you shouldn't use plpgsql at all: a simple
SQL function gets the job done with a lot less notational overhead
(and likely less runtime overhead too).

postgres=# CREATE OR REPLACE FUNCTION query_time2( out procpid integer, out
postgres(# client_addr inet, out 
postgres(# query_time interval, out current_query text ) 
postgres-# returns setof record as $$
postgres$# SELECT procpid, client_addr, (now() - query_start),
postgres$# current_query
postgres$# FROM pg_stat_activity
postgres$# ORDER BY (now() - query_start) DESC;
postgres$# $$ language sql;
CREATE FUNCTION
postgres=# select * from query_time2();
 procpid | client_addr | query_time |        current_query         
---------+-------------+------------+------------------------------
    9874 |             | 00:00:00   | select * from query_time2();
(1 row)

postgres=# 

			regards, tom lane