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

["A. Kretschmer" <andreas(dot)kretschmer(at)schollglas(dot)com>]

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