Higher Order Mysql

Advanced Stored Routines techniques

Higher-order functions in math and computer science are functions which:
take one or more functions as an input
OR
output a function
in MySQL, we’re talking about stored routines that create other stored routines.

Why?
Pivot tables,
Global table checksums
Getters and setters
Multi-row insert query makers
Iterators
Dynamic functions allow you to access data from INFORMATION_SCHEMA and create dedicated dynamic routines and overcome syntax that does not accept variables.

Officially, MySQL stored routines can’t do it.

However, Guiseppe is a really smart guy (as I’m learning firsthand this week)…

One way:
You can create a text definition for a stored procedure from a routine, even though you can’t actually create the stored procedure. Get the text back into the client, and you’re all set! ie, pass text back to client, which client just passes back to MySQL. (genius, just genius).

ie
CREATE FUNCTION make_f_test()
returns text
return
'CREATE FUNCTION hi()
returns text return "hi" ';

get output of code into a variable and then execute that string.

Another way:
Don’t do this!
not recommended by MySQL AB, may not work in the future, may damage your data.
DON’T USE IT.

That being said, if you get:
ERROR 1457 (HY000):Failed to load routine xxx.yyyy.
The table mysql.proc is missig, corrupt or containts bad data (internal code -6)

then you did it wrong. Fixing this error is NOT trivial.

Create text, like in the last example, and change the mysql.proc table. Close the mysql connection, and then open a new one. When you open a new connection, the existing stored procedures and functions are cached. So you can only make 1 function with the make_routine function per session.

To change the mysql.proc code:
http://www.stardata.it/code/

Install the make_routine function (need full “mysql” db access)
Make a limited user who has access to the lib database.

(the make_routine function takes the following parameters)
database
name
type
params
return_type
actual code

He showed an example: given a set of values, find a record and give the primary key of the record; if the record does not exist, insert it and give the new primary key.

Another example: A function that gets all values for one field from a table and joins into a string (called COLUMN_CONCAT). One can, of course, do this with GROUP_CONCAT but only if the total character count is 1024 (otherwise it truncates it).

1. create a function that accepts paramaters
2. create a string (a ‘template’) with the function with placeholders
3. replace placeholders in template with paramaters (using REPLACE)
4. call make_routine using the template string

Advanced Stored Routines techniques

Higher-order functions in math and computer science are functions which:
take one or more functions as an input
OR
output a function
in MySQL, we’re talking about stored routines that create other stored routines.

Why?
Pivot tables,
Global table checksums
Getters and setters
Multi-row insert query makers
Iterators
Dynamic functions allow you to access data from INFORMATION_SCHEMA and create dedicated dynamic routines and overcome syntax that does not accept variables.

Officially, MySQL stored routines can’t do it.

However, Guiseppe is a really smart guy (as I’m learning firsthand this week)…

One way:
You can create a text definition for a stored procedure from a routine, even though you can’t actually create the stored procedure. Get the text back into the client, and you’re all set! ie, pass text back to client, which client just passes back to MySQL. (genius, just genius).

ie
CREATE FUNCTION make_f_test()
returns text
return
'CREATE FUNCTION hi()
returns text return "hi" ';

get output of code into a variable and then execute that string.

Another way:
Don’t do this!
not recommended by MySQL AB, may not work in the future, may damage your data.
DON’T USE IT.

That being said, if you get:
ERROR 1457 (HY000):Failed to load routine xxx.yyyy.
The table mysql.proc is missig, corrupt or containts bad data (internal code -6)

then you did it wrong. Fixing this error is NOT trivial.

Create text, like in the last example, and change the mysql.proc table. Close the mysql connection, and then open a new one. When you open a new connection, the existing stored procedures and functions are cached. So you can only make 1 function with the make_routine function per session.

To change the mysql.proc code:
http://www.stardata.it/code/

Install the make_routine function (need full “mysql” db access)
Make a limited user who has access to the lib database.

(the make_routine function takes the following parameters)
database
name
type
params
return_type
actual code

He showed an example: given a set of values, find a record and give the primary key of the record; if the record does not exist, insert it and give the new primary key.

Another example: A function that gets all values for one field from a table and joins into a string (called COLUMN_CONCAT). One can, of course, do this with GROUP_CONCAT but only if the total character count is 1024 (otherwise it truncates it).

1. create a function that accepts paramaters
2. create a string (a ‘template’) with the function with placeholders
3. replace placeholders in template with paramaters (using REPLACE)
4. call make_routine using the template string