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 === ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend
