MySQL - Create a function from another function

I want to create a function same like my old functions.

When I create a table, I can use `AS to other table as follows:

CREATE TABLE dummy2 AS SELECT * FROM dummy1;

Can I do the same thing with functions?

You could play some games with mysql.proc, the physical home of all Stored Procedures and Stored Functions.

First, here are the databases on my PC

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| david              |
| dufran             |
| jimelliott         |
| junk               |
| karto              |
| mysql              |
| performance_schema |
| preeti             |
| rolando            |
| sandro             |
| telwit             |
| telwit_french      |
| test               |
| user1162541        |
| yellowseason       |
+--------------------+
16 rows in set (0.00 sec)

mysql>

Let's create a Stored Function in the database rolando called GetUnixTimeStamp:

mysql> DELIMITER $
mysql> DROP FUNCTION IF EXISTS `GetUnixTimeStamp` $
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE DEFINER=`root`@`localhost` FUNCTION `GetUnixTimeStamp`(x INT) RETURNS int(11)
    -> DETERMINISTIC
    -> BEGIN
    ->     RETURN UNIX_TIMESTAMP();
    -> END $
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;
mysql> SELECT rolando.GetUnixTimeStamp(9);
+-----------------------------+
| rolando.GetUnixTimeStamp(9) |
+-----------------------------+
|                  1352263567 |
+-----------------------------+
1 row in set (0.00 sec)

mysql>

Let's try to sculpt the CREATE FUNCTION SQL Statement based on the function itself

SELECT CONCAT('CREATE DEFINER=`',REPLACE(definer,'@','`@`'),
'` ',type,' `',db,'`.`',name,'`(',param_list,') ',
IF(type='FUNCTION',CONCAT('RETURNS ',returns),''),' ',
IF(is_deterministic='YES','DETERMINISTIC',''),' ',body)
INTO @ProcedureCreationSQL from mysql.proc
WHERE db='rolando' AND name='GetUnixTimeSTamp';
SELECT @ProcedureCreationSQL\G

What does this generate ?

mysql> SELECT CONCAT('CREATE DEFINER=`',REPLACE(definer,'@','`@`'),
    -> '` ',type,' `',db,'`.`',name,'`(',param_list,') ',
    -> IF(type='FUNCTION',CONCAT('RETURNS ',returns),''),' ',
    -> IF(is_deterministic='YES','DETERMINISTIC',''),' ',body)
    -> INTO @ProcedureCreationSQL from mysql.proc
    -> WHERE db='rolando' AND name='GetUnixTimeSTamp';
Query OK, 1 row affected (0.00 sec)

mysql> SELECT @ProcedureCreationSQL\G
*************************** 1. row ***************************
@ProcedureCreationSQL: CREATE DEFINER=`root`@`localhost` FUNCTION `rolando`.`GetUnixTimeStamp`(x INT) RETURNS int(11) DETERMINISTIC BEGIN
    RETURN UNIX_TIMESTAMP();
END
1 row in set (0.00 sec)

mysql>

OK so far, so good. Let's change the SQL to place this function in the database david

mysql> SELECT CONCAT('CREATE DEFINER=`',REPLACE(definer,'@','`@`'),
    -> '` ',type,' `david`.`',name,'`(',param_list,') ',
    -> IF(type='FUNCTION',CONCAT('RETURNS ',returns),''),' ',
    -> IF(is_deterministic='YES','DETERMINISTIC',''),' ',body)
    -> INTO @ProcedureCreationSQL from mysql.proc
    -> WHERE db='rolando' AND name='GetUnixTimeSTamp';
Query OK, 1 row affected (0.00 sec)

mysql> SELECT @ProcedureCreationSQL\G
*************************** 1. row ***************************
@ProcedureCreationSQL: CREATE DEFINER=`root`@`localhost` FUNCTION `david`.`GetUnixTimeStamp`(x INT) RETURNS int(11) DETERMINISTIC BEGIN
    RETURN UNIX_TIMESTAMP();
END
1 row in set (0.00 sec)

mysql>

OK Great. We can make the SQL. Can I execute the creation of the new function ???

echo DELIMITER $ > newproc.txt
mysql -uroot --delimiter="$" -ANe"SELECT REPLACE(CONCAT('CREATE DEFINER=`',REPLACE(definer,'@','`@`'),'` ',type,' `david`.`',name,'`(',param_list,') ',IF(type='FUNCTION',CONCAT('RETURNS ',returns),''),' ',IF(is_deterministic='YES','DETERMINISTIC',''),' ',body),'\n',' ') from mysql.proc WHERE db='rolando' AND name='GetUnixTimeSTamp';" >> newproc.txt
echo $ >> newproc.txt
mysql -uroot < newproc.txt

Does the function exist ???

mysql> show create function david.getunixtimestamp\G
*************************** 1. row ***************************
            Function: getunixtimestamp
            sql_mode:
     Create Function: CREATE DEFINER=`root`@`localhost` FUNCTION `getunixtimestamp`(x INT) RETURNS int(11)
    DETERMINISTIC
BEGIN     RETURN UNIX_TIMESTAMP(); END
character_set_client: cp850
collation_connection: cp850_general_ci
  Database Collation: latin1_swedish_ci
1 row in set (0.00 sec)

mysql>

Biggest questiion of all : Does the newly copied function run ???

mysql> select david.getunixtimestamp(8);
+---------------------------+
| david.getunixtimestamp(8) |
+---------------------------+
|                1352265404 |
+---------------------------+
1 row in set (0.00 sec)

mysql>

HOORAY !!! This is how you can copy a function from one database to another.

FINAL QUESTION : Is this all worth it ???
SUMMARY

Here is the original SQL to generate function creation

``
SELECT CONCAT('CREATE DEFINER=',REPLACE(definer,'@','@'),
' ',type,' david.',name,'`(',param_list,') ',
IF(type='FUNCTION',CONCAT('RETURNS ',returns

StackExchange Database Administrators Q#28304, answer score: 2