Обсуждение: Fwd: 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;
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