On Fri, 2007-10-26 at 08:35 +0200, Reg Me Please wrote: > You could try this: > > > CREATE OR REPLACE FUNCTION foo( out procpid integer, out client_addr inet, out > query_time interval, out current_query text ) > RETURNS SETOF RECORD AS $BODY$ > ... > $BODY$ LANGUAGE PLPGSQL VOLATILE; Somehow it doesn't work.. CREATE OR REPLACE FUNCTION query_time2( out procpid integer, out client_addr inet, out query_time interval, out current_query text ) AS --RETURNS SETOF RECORD AS $BODY$ BEGIN SELECT procpid, client_addr, (now() - query_start), current_query FROM pg_stat_activity ORDER BY (now() - query_start) DESC; RETURN; END; $BODY$ LANGUAGE 'plpgsql' VOLATILE; ERROR: query has no destination for result data HINT: If you want to discard the results of a SELECT, use PERFORM instead. CONTEXT: PL/pgSQL function "query_time2" line 3 at SQL statement > > Il Friday 26 October 2007 08:24:46 Ow Mun Heng ha scritto: > > 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 ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings
