From: | Ow Mun Heng <Ow(dot)Mun(dot)Heng(at)wdc(dot)com> |
---|---|
To: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Query_time SQL as a function w/o creating a new type |
Date: | 2007-10-26 06:24:46 |
Message-ID: | 1193379886.10340.6.camel@neuromancer.home.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Thomas Finneid | 2007-10-26 06:25:09 | Re: select count() out of memory |
Previous Message | Joe Conway | 2007-10-26 05:45:20 | Re: [GENERAL] Crosstab Problems |