Skip to content

Instantly share code, notes, and snippets.

@DaveLeonard
Created November 6, 2020 07:15
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
Star You must be signed in to star a gist
Save DaveLeonard/2bffb2bd32459d9a76917a1284b1c392 to your computer and use it in GitHub Desktop.
Stored Procedure that will generate a view with dynamic unions
CREATE PROCEDURE `dyn_view_maker`()
READS SQL DATA
BEGIN
DECLARE schemaName VARCHAR(64);
DECLARE tablesName VARCHAR(64);
DECLARE done INT DEFAULT FALSE;
DECLARE cursor1 CURSOR FOR
SELECT table_schema, table_name
FROM information_schema.TABLES
WHERE table_schema = ‘your schema name’
AND table_name LIKE ‘table_temp_2_%’;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
SET @queryString = ‘’;
OPEN cursor1;
read_loop: LOOP — loop over the rows returned by cursor
FETCH cursor1 INTO schemaName, tablesName;
IF done THEN
LEAVE read_loop;
END IF;
IF @queryString = ‘’ THEN
SET @queryString := CONCAT(‘CREATE OR REPLACE VIEW v_your_view name AS SELECT … FROM `’, schemaName, ‘`.`’, tablesName, ‘` JOIN … AS … WHERE …’);
ELSE
SET @queryString := CONCAT(@queryString, ‘ UNION ALL SELECT … FROM `’, schemaName, ‘`.`’, tablesName, ‘` JOIN … AS … WHERE …’);
END IF;
END LOOP;
CLOSE cursor1;
select @queryString;
PREPARE stmt FROM @queryString;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment