Help with function

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;

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

Just curious but since which version these IN/OUT parameters are supported?

-- Matthias

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of A. Kretschmer
Sent: Wednesday, October 04, 2006 4:01 PM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Fwd: Help with function

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

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

am Wed, dem 04.10.2006, um 16:06:40 +0200 mailte Matthias.Pitzl@izb.de folgendes:

Just curious but since which version these IN/OUT parameters are supported?

I'm not sure, 8.0 or 8.1

[ silly fullquote deleted ]

Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47215, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net

am Wed, dem 04.10.2006, um 16:06:40 +0200 mailte Matthias.Pitzl@izb.de folgendes:

Just curious but since which version these IN/OUT parameters are supported?

Since 8.1:
http://developer.postgresql.org/pgdocs/postgres/release-8-1.html
E.6.3.8. General Server-Side Language Changes
Allow SQL and PL/PgSQL functions to use OUT and INOUT parameters (Tom)

HTH, Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47215, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net