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

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