am 19.08.2005, um 16:38:20 +0100 mailte Nigel Horne folgendes:
> On Fri, 2005-08-19 at 16:30, Adam Witney wrote:
> > > I can't work out from that how to return more than one value.
> >
> > Hi Nigel,
> >
> > Add SETOF to your function like so:
> >
> > CREATE TABLE test (id int);
> > INSERT INTO test VALUES(1);
> > INSERT INTO test VALUES(2);
> >
> > CREATE FUNCTION test_func() RETURNS SETOF integer AS '
> > SELECT id FROM test;
> > ' LANGUAGE SQL;
>
> What if one value I want to return is an integer, and another one is a
> string?
Create a new type. For instance: (sorry, comments in german)
,----
| create type saldeninfo as (kontonr bigint,
| zugang numeric(10,2),
| abgang numeric(10,2),
| zeitpunkt timestamp,
| saldo numeric(10,2));
|
| --
| -- Nun die Funktion, sie ist in der Sprache SQL definiert
| --
| create or replace function saldeninfo( int ) returns setof saldeninfo as
| $$
| select reset_saldo();
| select kontonr, case when typ = 'Z' then wert when typ = 'A' then NULL END as zugang,
| case when typ = 'Z' then NULL when typ = 'A' then wert end as abgang,
| ts,
| prev_saldo(get_saldo(typ, wert))::numeric(10,2) as saldo
| from buchungen where kontonr = $1;
| $$
| language sql;
|
`----
Regards, Andreas
--
Andreas Kretschmer (Kontakt: siehe Header)
Heynitz: 035242/47212, D1: 0160/7141639
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net
=== Schollglas Unternehmensgruppe ===
