From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | "A(dot) Kretschmer" <andreas(dot)kretschmer(at)schollglas(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Query_time SQL as a function w/o creating a new type |
Date: | 2007-10-26 12:47:06 |
Message-ID: | 9899.1193402826@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
"A. Kretschmer" <andreas(dot)kretschmer(at)schollglas(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Martijn van Oosterhout | 2007-10-26 13:02:21 | Re: INDEX and JOINs |
Previous Message | Reg Me Please | 2007-10-26 12:39:28 | Re: INDEX and JOINs |