Re: [GENERAL] Fwd: Help with function
am Wed, dem 04.10.2006, um 16:06:40 +0200 mailte [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Fwd: Help with function
am Wed, dem 04.10.2006, um 16:06:40 +0200 mailte [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Fwd: Help with function
Just curious but since which version these IN/OUT parameters are supported? -- Matthias > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] 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 > > dbNamevarchar; > > activeTransactionsinteger; > > countRecrecord; > > 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 > dbNamevarchar; > activeTransactionsinteger; > countRecrecord; > 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 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
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 > dbNamevarchar; > activeTransactionsinteger; > countRecrecord; > 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 dbNamevarchar; activeTransactionsinteger; countRecrecord; 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
[GENERAL] Fwd: Help with function
I did not see this go through.Chris-- Forwarded message --From: Chris Hoover <[EMAIL PROTECTED] >Date: Oct 3, 2006 4:49 PMSubject: Help with functionTo: pgsql-general@postgresql.orgI 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;
