Search Postgresql Archives

Re: help w/ SRF function

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux