Обсуждение: Query_time SQL as a function w/o creating a new type

Поиск
Список
Период
Сортировка

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

От
Ow Mun Heng
Дата:
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.

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

От
Reg Me Please
Дата:
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

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

От
Ow Mun Heng
Дата:
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

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

От
"A. Kretschmer"
Дата:
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

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

От
Tom Lane
Дата:
"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