SEO

#1 (**permalink**) 04-09-2008, 11:40 AM

I did not see this go through.

Chris

---------- Forwarded message ----------
From: Chris Hoover <revoohc@gmail.com>
Date: Oct 3, 2006 4:49 PM
Subject: Help with function
To: pgsql-general@postgresql.org

I need some help with writing a plpgsql function. I want to return multiple
items from the function. How do I do this?

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;

#2 (**permalink**) 04-09-2008, 11:40 AM

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/x....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

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

Fwd: Help with function