From: Peter Brawley
Date: April 13 2007 10:48pm
Subject: Re: creating a function in mysql
List-Archive: http://lists.mysql.com/mysql/206154
Message-Id: <462008C9.4080108@earthlink.net>
MIME-Version: 1.0
Content-Type: multipart/alternative;
 boundary="------------000309040506060805060309"

--------------000309040506060805060309
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

 >Does MySql support using it's own functions within this code?DROP 
FUNCTION IF EXISTS testconcat;

Works for me (though CONCAT maddenly trims its args):

DELIMITER |
CREATE FUNCTION testconcat( str CHAR(20) )
RETURNS CHAR(50)
DETERMINISTIC
BEGIN
  SET str = CONCAT( str, ' ', 'suffix' );
  RETURN str;
END;
|
DELIMITER ;
select testconcat('this has a ' ) as test;
+-------------------+
| test              |
+-------------------+
| this has a suffix |
+-------------------+
PB

-----

Andrey Dmitriev wrote:
> Thanks, that works..
>  
> Next question
>  
> Does MySql support using it's own functions within this code?
>  
> E.g. I tried
>  
> set str = select concat (str,'hello');
> set str = concat (str, 'hello');
>  
> And it didn't seem to like either.
>  
> Thanks,
> -andrey
>
>   _____  
>
> From: Peter Brawley [mailto:peter.brawley@stripped] 
> Sent: Friday, April 13, 2007 5:37 PM
> To: Andrey Dmitriev
> Cc: mysql@stripped
> Subject: Re: creating a function in mysql
>
>
>
>   
>> I've determined that I don't seem to 
>> be able to create functions at all.
>>     
> Declare a delimiter, and lose the quote around the function name, eg
>
> DROP FUNCTION IF EXISTS weighted_average;
> DELIMITER |
> CREATE FUNCTION weighted_average(n1 INT, n2 INT, n3 INT, n4 INT) RETURNS
> int(11)
> DETERMINISTIC
> BEGIN
>   DECLARE avg INT;
>   SET avg = (n1+n2+n3*2+n4*4)/8;
>   RETURN avg;
> END;
> |
> DELIMITER ;
>
> PB
>
> -----
>
> Andrey Dmitriev wrote: 
>
>    All,
>    
>    I've been trying to create a function that will generate URLs so that
>    I wouldn't have to wrote ugly SQL all the time. After no success,
>    I've determined that I don't seem to be able to create functions at
>    all. I am relatively new to mysql development (or management), so any
>    advice is appreciated (coming from oracle world, btw)
>    
>    [root@mt-adm1 ~]# rpm -qa|grep -i mysql
>    MySQL-shared-standard-5.0.18-0.rhel4
>    mysql-gui-tools-5.0r3-1rhel4
>    mysqlclient10-3.23.58-4.RHEL4.1
>    mysql-administrator-5.0r3-1rhel4
>    MySQL-server-pro-5.0.18-0.rhel4
>    MySQL-client-pro-5.0.18-0.rhel4
>    
>    following:
>    http://www.databasejournal.com/features/mysql/article.php/10897_35698
>    46_2
>    
>    getting
>    Database changed
>    mysql> CREATE FUNCTION 'WEIGHTED_AVERAGE'(n1 INT, n2 INT, n3 INT, n4
>    INT)
>        ->  RETURNS int(11)
>        ->     DETERMINISTIC
>        -> BEGIN
>        ->    DECLARE avg INT;
>    ERROR 1064 (42000): You have an error in your SQL syntax; check the
>    manual that corresponds to your MySQL server version for the right
>    syntax to use near ''WEIGHTED_AVERAGE'(n1 INT, n2 INT, n3 INT, n4
>    INT)
>     RETURNS int(11)
>        DETERMI' at line 1
>    mysql>    SET avg = (n1+n2+n3*2+n4*4)/8;
>    ERROR 1193 (HY000): Unknown system variable 'avg'
>    mysql>    RETURN avg;
>    ERROR 1064 (42000): You have an error in your SQL syntax; check the
>    manual that corresponds to your MySQL server version for the right
>    syntax to use near 'RETURN avg' at line 1
>    mysql>   END
>        ->
>        -> ;
>    ERROR 1064 (42000): You have an error in your SQL syntax; check the
>    manual that corresponds to your MySQL server version for the right
>    syntax to use near 'END' at line 1
>    mysql>
>    
>    
>    
>    
>      
>    
>   _____  
>
>
>    No virus found in this incoming message.
>    Checked by AVG Free Edition.
>    Version: 7.5.446 / Virus Database: 269.4.0/759 - Release Date:
>    4/12/2007 7:58 PM
>      
>
>
>
>   
> ------------------------------------------------------------------------
>
> No virus found in this incoming message.
> Checked by AVG Free Edition.
> Version: 7.5.446 / Virus Database: 269.4.0/759 - Release Date: 4/12/2007 7:58 PM
>   

--------------000309040506060805060309--