Passing table as function's parameter
I created a function and I need to pass table as input parameter.
I'm using MySQL 5.5.
A code is bellow:
When I try to use the function like this
CREATE DEFINER=`root`@`localhost` FUNCTION `get_company_rating`(`company_id` INT, `company_table` VARCHAR(30), `company_foreign_key` VARCHAR(20))
SQL SECURITY DEFINER
DECLARE rating FLOAT;
DECLARE result FLOAT;
SELECT AVG(ratings.mark) INTO rating
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;
SET result = 0;
ELSE SET result = rating;
I have an error: "SQL Error (1054): Unknown column 'bankleverandor_ratings' in 'field list'"
SELECT get_company_rating(12, bankleverandor_ratings, `fpid`) AS rating
When I call function as bellow:
I have such error: "Table company_table does not exists".
SELECT get_company_rating(12, 'bankleverandor_ratings', `fpid`) AS rating
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?
You must PREPARE the sql however, then EXECUTE the statement.
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:
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 03:46 PM.
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?
FROM table join table2 using(table1_ID)
ot1 as table, offtable1.fkey as fkey, other, stuff from offtable_1
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 02:07 PM.
Users Browsing this Thread
There are currently 1 users browsing this thread. (0 members and 1 guests)