Re: [GENERAL] help w/ SRF function
On Tue, 2007-09-18 at 02:24 -0700, Trevor Talbot wrote: On 9/17/07, Ow Mun Heng [EMAIL PROTECTED] 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. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] help w/ SRF function
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 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] help w/ SRF function
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. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] 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 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] help w/ SRF function
On 9/17/07, Ow Mun Heng [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] help w/ SRF function
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. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] 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; ^ ---(end of broadcast)--- TIP 6: explain analyze is your friend