DROP PROCEDURE IF EXISTS create_mview;

DELIMITER //
CREATE PROCEDURE create_mview (IN source_table VARCHAR(150), IN primary_key VARCHAR(150), IN target_table  VARCHAR(150))
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
MODIFIES SQL DATA
BEGIN
-- declare some variables ...
DECLARE m_view_name VARCHAR(150);
DECLARE create_m_view_sql TEXT;
DECLARE drop_m_view_sql TEXT;
DECLARE add_primary_key_sql TEXT;

-- create the sql queries to drop the materialized view, create the materialized view
-- and add a primary key to the materialized view.
SET m_view_name:=concat('view_m_', source_table);
SET @drop_m_view_sql:=concat('DROP TABLE IF EXISTS ',target_table);
SET @create_m_view_sql:=concat('CREATE TABLE ', target_table , ' SELECT * FROM ', source_table);
SET @add_primary_key_sql:=concat('ALTER TABLE ', target_table , ' ADD PRIMARY KEY (', primary_key ,')');



-- drop the materialized view if it exists
PREPARE stmt1 FROM @drop_m_view_sql;
EXECUTE stmt1;

-- create the table
PREPARE stmt2 FROM @create_m_view_sql;
EXECUTE stmt2;

-- add the primary key
PREPARE stmt3 FROM @add_primary_key_sql;
EXECUTE stmt3;

deallocate prepare stmt1;
deallocate prepare stmt2;
deallocate prepare stmt3;

END; //
DELIMITER ;