am Wed, dem 04.10.2006, um 9:31:28 -0400 mailte Chris Hoover folgendes:
> Here is my attempt at the function (note, this is a simple example that could
> obviously be done via a view, but I am trying to learn more about writing
> plpgsql functions):
>
> create or replace function dba.active_transactions_by_db() returns setof
> integer pg_stat_activity.datname%TYPE as
> $BODY$
> declare
> dbName varchar;
> activeTransactions integer;
> countRec record;
> begin
> for countRec in select count(1) as cnt, datname from pg_stat_activity group
> by datname loop
> return next countRec;
> end loop;
>
> return countRec;
> end;
> $BODY$
> language plpgsql;
I wrote for you this:
create or replace function active_transactions_by_db(out _cnt int, out _datname text) returns setof record as
$BODY$
declare
dbName varchar;
activeTransactions integer;
countRec record;
begin
for countRec in select count(1) as cnt, datname from pg_stat_activity group by datname loop
_cnt := countRec.cnt;
_datname := countRec.datname;
return next;
end loop;
return;
end;
$BODY$
language plpgsql;
It works.
If you want lern more about IN and OUT - Parameters, see:
http://people.planetpostgresql.org/xzilla/index.php?/archives/149-out-parameter-sql-plpgsql-examples.html#extended
HTH, Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47215, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net