Re: help w/ SRF function

On Mon, 2007-09-17 at 09:42 +0800, Ow Mun Heng wrote:
> 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;

anyone knows how come I can't use the reference fromdate/todate etc or
use aliases but have to resort to using $1/$2 etc?

Many Thanks