Thread: help w/ SRF function

Hi,

I want to use a SRF to return multi rows.

current SRF is pretty static.

create type foo_type as (
id smallint
data1 int
data2 int
)

CREATE OR REPLACE FUNCTION foo_func()
  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 '8/1/2007' AND '9/8/2007'
    And D.code IN ('ID_123')
$BODY$
LANGUAGE 'sql' IMMUTABLE STRICT;

I would like for the above to be a little bit more dynamic in that the
start_timestamp and the code can be input-fields.

eg:

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.

Or should I use plpgsql as SQL cannot handle variable substitution?

What about doing  dynamic SQL eg:

Dsql = select X,Y,Z from foo, join bar on bar.a = foo.a
       where D.start_timestamp between ' || fromdate ||' and ' ||
todate||'

execute DSQL

Thanks for any/all help.

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

On Wed, 2007-09-19 at 07:57 +0200, A. Kretschmer wrote:
> 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.


Yep.. that works as advertised.