Postgres Pro
Downloads
Home   >   mailing lists

Thread: Fwd: Help with function

Fwd: Help with function

From
"Chris Hoover"
Date:
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;

Re: Fwd: Help with function

From
"A. Kretschmer"
Date:
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

Re: Fwd: Help with function

From
Matthias.Pitzl@izb.de
Date:
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

Re: Fwd: Help with function

From
"A. Kretschmer"
Date:
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

Re: Fwd: Help with function

From
"A. Kretschmer"
Date:
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