MySQL Forums
Forum List  »  Partitioning

Add new LIST partition based on passed id in stored proc?
Posted by: Michael Zatkovetsky
Date: September 08, 2020 08:20PM

Hi! Thank you for taking a look.
I have a table with LIST partitioning. We need to have each client in its own partition:

CREATE TABLE `mydb`.`test` (
`client_id` INT NOT NULL,
`client_name` VARCHAR(45) NULL,
PRIMARY KEY (`client_id`));

ALTER TABLE test PARTITION BY LIST (client_id)
(partition p100 values in (100),
partition p101 values in (101));


I am trying to create a stored procedure to be called when a new client is created:


DELIMITER $$
USE `mydb`$$
CREATE PROCEDURE `util_partioning_partition_new`(IN p_client_id INT)
BEGIN
DECLARE partition_name varchar(25);
select CONCAT('p', p_client_id) into partition_name;
ALTER TABLE test ADD PARTITION (PARTITION partition_name values in (p_client_id));
END$$

DELIMITER ;
;

and getting this error:
Apply changes to util_partioning_partition_new Error 1064: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed near 'p_client_id));

PLEASE HELP! How do I pass client ID to the values in()?

Many thanks in advance,

Options: ReplyQuote


Subject
Views
Written By
Posted
Add new LIST partition based on passed id in stored proc?
882
September 08, 2020 08:20PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.