Can we place a temporary table inside a stored function
Just need to know if we can place a temporary table inside the stored function ?
I am not offering a solution, or a boolean response. What have you found in your testing/attempts?
On Tue, Jun 27, 2023 at 8:27 PM manpritsinghece--- via discuss < discuss@lists.mariadb.org> wrote:
Just need to know if we can place a temporary table inside the stored function ? _______________________________________________ discuss mailing list -- discuss@lists.mariadb.org To unsubscribe send an email to discuss-leave@lists.mariadb.org
You can certainly _use_ a temporary memory table from inside a stored function but you should create the table earlier before calling the stored function and you may want to truncate the table at the start/end of the function.
On Tue, 27 Jun 2023 at 17:27, manpritsinghece--- via discuss < discuss@lists.mariadb.org> wrote:
Just need to know if we can place a temporary table inside the stored function ? _______________________________________________ discuss mailing list -- discuss@lists.mariadb.org To unsubscribe send an email to discuss-leave@lists.mariadb.org
Consider a problem where i have to find the second largest value in each row : I have tried to write code for that CREATE TABLE xyz(a INT, b INT, c INT); INSERT INTO xyz VALUES (3, 7, 5), (5, 6, 2), (9, 3, 6), (5, 1, 3), (2, 4, 7);
CREATE TEMPORARY TABLE tb(g INT); DELIMITER // CREATE FUNCTION second_largest(x INT, y INT, z INT) RETURNS INT BEGIN DECLARE ans INT; INSERT INTO tb VALUES (x), (y), (z); SET ans = (SELECT g FROM tb ORDER BY g DESC LIMIT 1, 1 ); DELETE FROM tb; RETURN ans; END // DELIMITER ;
SELECT second_largest(a, b, c) FROM xyz; SELECT second_largest(4, 1, 8);
The function is working ok ...
I just need to know if the way i have used the temporary table inside the function is ok or not
You don't want to do that. Your memory temptable should have a primary key and it generally uses a hash index so you definitely don't want to use order by unless you explicitly create it to use btree index... And for such a small number of values, what is wrong with using a small number of comparisons?
On Wed, 28 Jun 2023, 9:36 am manpritsinghece--- via discuss, < discuss@lists.mariadb.org> wrote:
Consider a problem where i have to find the second largest value in each row : I have tried to write code for that CREATE TABLE xyz(a INT, b INT, c INT); INSERT INTO xyz VALUES (3, 7, 5), (5, 6, 2), (9, 3, 6), (5, 1, 3), (2, 4, 7);
CREATE TEMPORARY TABLE tb(g INT); DELIMITER // CREATE FUNCTION second_largest(x INT, y INT, z INT) RETURNS INT BEGIN DECLARE ans INT; INSERT INTO tb VALUES (x), (y), (z); SET ans = (SELECT g FROM tb ORDER BY g DESC LIMIT 1, 1 ); DELETE FROM tb; RETURN ans; END // DELIMITER ;
SELECT second_largest(a, b, c) FROM xyz; SELECT second_largest(4, 1, 8);
The function is working ok ...
I just need to know if the way i have used the temporary table inside the function is ok or not _______________________________________________ discuss mailing list -- discuss@lists.mariadb.org To unsubscribe send an email to discuss-leave@lists.mariadb.org
This is something learning by doing. What about this function
CREATE FUNCTION second_largest(x INT, y INT, z INT) RETURNS INT DETERMINISTIC RETURN x+y+z - LEAST(x, y, z) - GREATEST(x, y, z);
See i believe in code clarity, although this seems using two function calls inside my function . may be expensive in terms of time, but is more clear .
Function calls are very cheap compared to access any table. Internal functions are cheapest.
On Thu, 29 Jun 2023, 7:49 am manpritsinghece--- via discuss, < discuss@lists.mariadb.org> wrote:
This is something learning by doing. What about this function
CREATE FUNCTION second_largest(x INT, y INT, z INT) RETURNS INT DETERMINISTIC RETURN x+y+z - LEAST(x, y, z) - GREATEST(x, y, z);
See i believe in code clarity, although this seems using two function calls inside my function . may be expensive in terms of time, but is more clear . _______________________________________________ discuss mailing list -- discuss@lists.mariadb.org To unsubscribe send an email to discuss-leave@lists.mariadb.org
participants (3)
-
Antony T Curtis -
Jeff Dyke -
manpritsinghece＠gmail.com