Infinite Arguments for UDF

Ikviens · #1 (**permalink**) 07-18-11, 09:04

Hi all,

A while ago, I've begun to wonder in a thread if I can write up a user defined function in MySQL for printing table codes.

I've come up with the following two-column code but have no idea how to scale it to allow indefinite number of arguments.

Code:

mysql> SELECT
    -> CONCAT('<tr>'
    ->      , CONCAT('<td>'
    ->             , CONCAT_WS('</td><td>'
    ->                       , IFNULL(id,'NULL')
    ->                       , IFNULL(amount,'NULL')
    ->                       , IFNULL(NULL,'NULL')
    ->               )
    ->             , '</td>'
    ->        )
    ->      , '</tr>') asdf
    ->   FROM product;
+----------------------------------------------+
| asdf                                         |
+----------------------------------------------+
| <tr><td>1</td><td>100</td><td>NULL</td></tr> | 
| <tr><td>2</td><td>200</td><td>NULL</td></tr> | 
| <tr><td>3</td><td>300</td><td>NULL</td></tr> | 
| <tr><td>4</td><td>400</td><td>NULL</td></tr> | 
+----------------------------------------------+
4 rows in set (0.08 sec)

Can I create a UDF like CONCAT? Or do I have to write it in, shudder, C++ and compile it?

it-iss.com · #2 (**permalink**) 07-19-11, 02:09

I am not sure why you do not just pull the information from the database and from your application generate the HTML. If your client application is on a separate server to your database server you are transferring a lot more data by having the database generate the HTML than generating it yourself in your application. This will be more scalable too.

Ikviens · #3 (**permalink**) 07-19-11, 11:32

It's a strange request, so there is no wonder you ask why. There is no application in this case that deals with the representation layer because I am going to use this table-generating UDF for documents. It would be a very convenient tool when I copy result sets from the terminal into HTML or LaTeX documents.

Surely, I can write tables in docs using "export as HTML" command. I won't die for it but it'd be a lot nicer if I don't have to save "resultset.html" file every time I have a table to copy.

Maybe it is more a life-hack than a DB question. I should feel very lucky if someone ever holds my hand while I write an all-query solution. Still, I am wondering how, if at all, infinite arguments are possible in SQL functions as they are in programming languages.

it-iss.com · #4 (**permalink**) 07-19-11, 11:55

alternatively you can dynamically create SQL statements in stored procedures which can have its result set returned. I had created a mini example MySQL – stored procedures and dynamic SQL « IT Integrated Business Solutions

So what you do is generate the SQL dynamically within the stored procedure and then return this resultset. Would that help?

Ikviens · #5 (**permalink**) 07-27-11, 12:01

Ronan, thanks for the reference.

It took me a long time to create something very simplistic. At one time I tried to concatenate columns with a CASE expression and the got a collation error. So here is my code, albeit very unsatisfactory:

Code:

DROP PROCEDURE IF EXISTS html_table;
DELIMITER $$
CREATE PROCEDURE html_table(IN listOfColumns VARCHAR(100), tableName VARCHAR(100))
BEGIN
  SET @query := CONCAT("SELECT CONCAT('<tr>', CONCAT('<td>', CONCAT_WS('</td><td>',", listOfColumns, "), '</td>'), '</tr>') htmlTableTags FROM ", tableName);
  PREPARE stmt FROM @query;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
END;
$$

It takes column names and the table name and executes an unconditional SELECT statement (no JOIN or WHERE). Test results are as follows:

Code:

mysql> delimiter ;
mysql> call html_table( "bookid, title", "books" );
+-----------------------------------------+
| htmlTableTags                           |
+-----------------------------------------+
| <tr><td>1</td><td>abc</td></tr>         | 
| <tr><td>2</td><td>photography</td></tr> | 
| <tr><td>3</td><td>video</td></tr>       | 
| <tr><td>4</td><td>soccer</td></tr>      | 
| <tr><td>5</td><td>football</td></tr>    | 
| <tr><td>6</td><td>tennis</td></tr>      | 
| <tr><td>7</td><td>golf</td></tr>        | 
| <tr><td>8</td><td>fishing</td></tr>     | 
| <tr><td>9</td><td>cycling</td></tr>     | 
+-----------------------------------------+
9 rows in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Thanks again.

Internet Services

Web Development

Digital Marketing

Consumer Tech