Thread: Query_time SQL as a function w/o creating a new type
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.
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; (Thanks to Joen Conway for showing this in tablefunc!) 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@postgresql.org so that your > message can get through to the mailing list cleanly
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@postgresql.org so that your > > message can get through to the mailing list cleanly
am Fri, dem 26.10.2007, um 14:51:52 +0800 mailte Ow Mun Heng folgendes: > > 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 Change the SELECT procpid, ... to SELECT into procpid, ... Thats all (i hope)... Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
"A. Kretschmer" <andreas.kretschmer@schollglas.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