Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2006
    Posts
    56

    Unanswered: Infinite Arguments for UDF

    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?

  2. #2
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    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.
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  3. #3
    Join Date
    Mar 2006
    Posts
    56
    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.

  4. #4
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    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?
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  5. #5
    Join Date
    Mar 2006
    Posts
    56
    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.
    Last edited by Ikviens; 07-27-11 at 13:07.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •