help w/ SRF function

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

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

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

am Mon, dem 17.09.2007, um 9:21:22 +0800 mailte Ow Mun Heng folgendes:

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.

Use $1, 2 and $3 within the function-body instead fromdate, todate and
code.

Example:

test=# select * from n;
feld1 | feld2
--------+-------
Frank | 23
Frank | 31
Stefan | 32
Stefan | 22
Jochen | 29
(5 rows)

test=*# create or replace function nn(int) returns setof n as $$ select * from n where feld2=$1; $$ language sql;
CREATE FUNCTION
test=*# select * from nn(22);
feld1 | feld2
--------+-------
Stefan | 22
(1 row)

Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net

On Wed, 2007-09-19 at 07:57 +0200, A. Kretschmer wrote:

am Mon, dem 17.09.2007, um 9:21:22 +0800 mailte Ow Mun Heng folgendes:

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.

Use $1, 2 and $3 within the function-body instead fromdate, todate and
code.

Yep.. that works as advertised.

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.