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;
^
