Re: Query_time SQL as a function w/o creating a new type

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(at)postgresql(dot)org so that your
> > message can get through to the mailing list cleanly