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

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