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

[Ow Mun Heng <Ow.Mun.Heng@xxxxxxx>]

Hi,

After Erik Jones gave me the idea for this, I started to become lazy to
have to type this into the sql everytime I want to see how long a query
is taking.. so, I thought that I'll create a function to do just that..
I ended up with..

CREATE OR REPLACE FUNCTION query_time()
  RETURNS SETOF query_time AS
$BODY$
DECLARE
rec RECORD;

BEGIN
	FOR rec IN
	SELECT procpid, client_addr, now() - query_start as query_time,
	current_query
	FROM pg_stat_activity
	ORDER BY query_time DESC
	LOOP
	RETURN NEXT rec;
	END LOOP;
	RETURN;
END;

$BODY$
  LANGUAGE 'plpgsql' VOLATILE;


But the issue with the above is that I need to create a type.

CREATE TYPE query_time AS
   (procpid integer,
    client_addr inet,
    query_time interval,
    current_query text);

Is there a method which I'm able to return a result set w/o needing to declare/create a new type.

I tried to use language 'sql' but it only returned me 1 column, with all the fields concatenated together with
comma separating the fields.





---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
       subscribe-nomail command to majordomo@xxxxxxxxxxxxxx so that your
       message can get through to the mailing list cleanly