On Mon, 2007-09-17 at 09:21 +0800, Ow Mun Heng wrote: > 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. Seems like I found this after I posted the question. (Doh! Why does this always happen) Variable substition can happen using $1/$2/$3 notation. CREATE OR REPLACE FUNCTION foo_func(timestamp,timestamp,code) 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 $1 AND $2 And D.code IN ($3) $BODY$ LANGUAGE 'sql' IMMUTABLE STRICT; But If I were to use ALIASINg, I get an error eg: DECLARE DECLARE fromdate ALIAS for $1; todate ALIAS for $2; code ALIAS for $3; ERROR: syntax error at or near "ALIAS" LINE 5: fromdate ALIAS for $1; ^ ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend