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

"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