Thread: create function problem
I've used the following sql to create the function and function creation is also successful. create function usage(text) returns numeric as 'select ceil(sum(sessiontime)/60) from ath_online where loginname=\'$1\' and startdate >= (select startdate from cst_package where status=\'t\' and cid=(select cid from cst_customer where loginname=\'$1\'));' language 'sql'; executing this function as select usage('kancha'); returns no records. while executing the query directly replacke $1 with kancha returns result. Where did i go wrong ?? __________________________________________________ Do You Yahoo!? Yahoo! Sports - live college hoops coverage http://sports.yahoo.com/
Hi, > create function usage(text) > returns numeric > as 'select ceil(sum(sessiontime)/60) from ath_online > where loginname=\'$1\' and startdate >= (select > startdate from cst_package where status=\'t\' and > cid=(select cid from cst_customer where > loginname=\'$1\'));' > language 'sql'; Try to replace \'$1\' with just $1 (i.e. loginname = $1). Regards -- Matteo Beccati C.R.P. Software S.r.l. http://www.crpsoftware.it/
> Hi, > > > create function usage(text) > > returns numeric > > as 'select ceil(sum(sessiontime)/60) from > ath_online > > where loginname=\'$1\' and startdate >= (select > > startdate from cst_package where status=\'t\' and > > cid=(select cid from cst_customer where > > loginname=\'$1\'));' > > language 'sql'; > > Try to replace \'$1\' with just $1 (i.e. loginname = > $1). > the argument is a string so it has to be enclosed in quotes and \ is to escape the quote. replacing with just $1 will give error like following: Unable to identify an operator '=$' for types 'varchar' and 'int4' You will have to retype this query using an explicit cast __________________________________________________ Do You Yahoo!? Yahoo! Sports - live college hoops coverage http://sports.yahoo.com/
Kancha . wrote: > > > Hi, > > > > > create function usage(text) > > > returns numeric > > > as 'select ceil(sum(sessiontime)/60) from > > ath_online > > > where loginname=\'$1\' and startdate >= (select > > > startdate from cst_package where status=\'t\' and > > > cid=(select cid from cst_customer where > > > loginname=\'$1\'));' > > > language 'sql'; > > > > Try to replace \'$1\' with just $1 (i.e. loginname = > > $1). > > > > the argument is a string so it has to be enclosed in > quotes and \ is to escape the quote. replacing with > just $1 will give error like following: The argument is a string and $1 becomes a T_Param node of type "text" in your case, which is a perfectly valid string without the quotes. Don't get confused by the $ sign in the parameter name. $1 is treated like a variablename. Just try "exactly" what was suggested, including the whitespaces surrounding the = operator. Write loginname = $1 instead of loginname=$1 This does not only make the code more readable for humans. The parser likes it too. Jan > > Unable to identify an operator '=$' for types > 'varchar' and 'int4' > You will have to retype this query using an > explicit cast > > > > __________________________________________________ > Do You Yahoo!? > Yahoo! Sports - live college hoops coverage > http://sports.yahoo.com/ > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org > -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com # _________________________________________________________ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com