Postgres Pro
Downloads
Home   >   mailing lists

Thread: Re: help w/ SRF function

Re: help w/ SRF function

From
Ow Mun Heng
Date:
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;
              ^

Re: help w/ SRF function

From
Ow Mun Heng
Date:
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

Re: help w/ SRF function

From
"Trevor Talbot"
Date:
On 9/17/07, Ow Mun Heng <Ow.Mun.Heng@wdc.com> wrote:

> > > CREATE OR REPLACE FUNCTION foo_func(fromdate timestamp, todate
> > > timestamp, code text)

> > > 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?

You seem to be confusing SQL with PL/pgSQL.  If you want variables,
aliases, flow control etc instead of a simple macro, you need to use a
procedural language.

http://www.postgresql.org/docs/8.2/static/xfunc-sql.html
http://www.postgresql.org/docs/8.2/static/plpgsql.html

Re: help w/ SRF function

From
Ow Mun Heng
Date:
On Tue, 2007-09-18 at 02:24 -0700, Trevor Talbot wrote:
> On 9/17/07, Ow Mun Heng <Ow.Mun.Heng@wdc.com> wrote:
>
> > > > CREATE OR REPLACE FUNCTION foo_func(fromdate timestamp, todate
> > > > timestamp, code text)
>
> > > > 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?
>
> You seem to be confusing SQL with PL/pgSQL.  If you want variables,
> aliases, flow control etc instead of a simple macro, you need to use a
> procedural language.
>
> http://www.postgresql.org/docs/8.2/static/xfunc-sql.html
> http://www.postgresql.org/docs/8.2/static/plpgsql.htmll

Thanks. I've moved from SQL to plpgsql now. Thanks to your pointers and
ppl in IRC.