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:
Show quoted text
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
Show quoted text
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