[GENERAL] help w/ SRF function

[Ow Mun Heng]

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.

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

               http://archives.postgresql.org/