www.webdeveloper.com
Results 1 to 4 of 4

Thread: Passing table as function's parameter

  1. #1
    Join Date
    Jul 2011
    Posts
    131

    Passing table as function's parameter

    Hi,
    I created a function and I need to pass table as input parameter.
    I'm using MySQL 5.5.
    A code is bellow:
    Code:
    CREATE DEFINER=`root`@`localhost` FUNCTION `get_company_rating`(`company_id` INT, `company_table` VARCHAR(30), `company_foreign_key` VARCHAR(20))
    	RETURNS FLOAT
    	LANGUAGE SQL
    	NOT DETERMINISTIC
    	CONTAINS SQL
    	SQL SECURITY DEFINER
    	COMMENT ''
    BEGIN
    	DECLARE rating FLOAT;
    	DECLARE result FLOAT;
    	
    	SELECT AVG(ratings.mark) INTO rating
    		FROM ratings
    		LEFT JOIN rating_types ON (rating_types.id = ratings.type_id)
    		LEFT JOIN company_table ON (company_table.rating_id = ratings.id)
    		WHERE company_table.company_foreign_key = company_id AND rating_types.value = 0;
    	
    	IF(ISNULL(rating)) THEN
    		SET result = 0;
    	ELSE SET result = rating;
               END IF;
    	
    	RETURN result;
    
    END
    When I try to use the function like this
    Code:
    SELECT get_company_rating(12, bankleverandor_ratings, `fpid`) AS rating
    I have an error: "SQL Error (1054): Unknown column 'bankleverandor_ratings' in 'field list'"
    When I call function as bellow:
    Code:
    SELECT get_company_rating(12, 'bankleverandor_ratings', `fpid`) AS rating
    I have such error: "Table company_table does not exists".
    There are different company types and there is a separate table for each of them.
    And I want to write one universal function for all these tables.
    Is it possible to pass table name as input parameter?
    Thanks!

  2. #2
    Join Date
    Jul 2010
    Location
    /ramdisk/
    Posts
    865
    Yes.

    You must PREPARE the sql however, then EXECUTE the statement.

    Code:
    mysql> PREPARE stmt1 FROM 'SELECT SQRT(POW(?,2) + POW(?,2)) AS hypotenuse';
    mysql> SET @a = 3;
    mysql> SET @b = 4;
    mysql> EXECUTE stmt1 USING @a, @b;
    
    
    +------------+
    | hypotenuse |
    +------------+
    |          5 |
    +------------+
    mysql> DEALLOCATE PREPARE stmt1;

    Here is the docs:
    http://dev.mysql.com/doc/refman/5.1/...tatements.html

    Edit: Also to be more specific, you are not passing the table as a parameter. You are passing the table's name as a parameter.
    Last edited by eval(BadCode); 10-16-2011 at 02:46 PM.
    I use (, ; : -) as I please- instead of learning the English language specification: I decided to learn Scheme and Java;

  3. #3
    Join Date
    Jul 2011
    Posts
    131
    I tried to use dynamic sql, but functions doesn't support it.
    It can be used only in procedure.
    And I need to use the result of the function in SELECT query.
    I also tried to write a procedure with dynamic sql and then call it in the function but it didn't work.
    Do you have any ideas?

  4. #4
    Join Date
    Jul 2010
    Location
    /ramdisk/
    Posts
    865
    select
    *
    FROM table join table2 using(table1_ID)
    JOIN (
    SELECT
    ot1 as table, offtable1.fkey as fkey, other, stuff from offtable_1
    UNION
    ot2 as table, offtable2.fkey as fkey, other, stuff from offtable_2
    ) dt ON dt.fkey = table.key

    where concat(other, stuff) like '%I didn't try really hard on this one%';


    Not going to be pretty. You can sure do it though, but consider trying to normalize the data more. It just seems to me like you want nice pretty joins, but you have not normalized your data at all and fallen back onto something where you just make a table to put data into the db really quicky even if it doesn't make sense. *shrug*, maybe not. I haven't really seen your database, so I don't know.

    From my experience, creating a function that is not 100% non-deterministic doesn't do much for you. You can almost always, it seems, write it into the query in a way that's much faster. A function for something like an `ordinal suffix` makes sense, but a function for joining your table's doesn't make much sense to me- not when a parameter is going to be the name of the table you're joining.
    Last edited by eval(BadCode); 10-17-2011 at 01:07 PM.
    I use (, ; : -) as I please- instead of learning the English language specification: I decided to learn Scheme and Java;

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

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



Recent Articles