Re: help w/ SRF function

am Mon, dem 17.09.2007, um 9:21:22 +0800 mailte Ow Mun Heng folgendes:
> CREATE OR REPLACE FUNCTION foo_func(fromdate timestamp, todate
> timestamp, code text)
> RETURNS SETOF foo AS
> $BODY$
> SELECT
> TRH.ID,
> TRH.data1,
> TRH.data2,
> FROM D
> INNER JOIN TS
> ON TS.id = D.id
> inner join TRH
> on ts.id = trh.id
> WHERE D.start_timestamp BETWEEN fromdate AND todate
> And D.code IN (code)
> $BODY$
> LANGUAGE 'sql' IMMUTABLE STRICT;
>
> How can I go about this this? The above will fail due to missing columns
> fromdate/todate/code.

Use $1, 2 and $3 within the function-body instead fromdate, todate and
code.

Example:

test=# select * from n;
feld1 | feld2
--------+-------
Frank | 23
Frank | 31
Stefan | 32
Stefan | 22
Jochen | 29
(5 rows)

test=*# create or replace function nn(int) returns setof n as $$ select * from n where feld2=$1; $$ language sql;
CREATE FUNCTION
test=*# select * from nn(22);
feld1 | feld2
--------+-------
Stefan | 22
(1 row)

Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net