JDcCxzNY

1DROP FUNCTION IF EXISTS CREATE_COLUMN;
2CREATE FUNCTION CREATE_COLUMN(PRODUCT_ID INT) 
3RETURNS MEDIUMTEXT
4BEGIN
5    DECLARE PRODUCTS, STATUS MEDIUMTEXT;
6    DECLARE SINGLE_PRODUCT, SINGLE_STATUS, COL_NAME, COL_VALUE MEDIUMTEXT;
7    DECLARE indx INT DEFAULT 0;
8        SELECT json_unquote(json_extract(product_details, '$.neg_validation[*].product')) INTO PRODUCTS FROM product WHERE product_id = PRODUCT_ID;
9        SELECT json_unquote(json_extract(product_details, '$.neg_validation[*].status')) INTO STATUS FROM product WHERE product_id = PRODUCT_ID;
10
11        REPEAT
12            SET SINGLE_PRODUCT = JSON_EXTRACT(PRODUCTS, CONCAT("$[", indx, "]"));
13            SET SINGLE_STATUS = JSON_EXTRACT(STATUS, CONCAT("$[", indx, "]"));
14            SET COL_NAME = CONCAT('"','view_products_', TRIM(BOTH '"' FROM SINGLE_PRODUCT), '"');
15            SET COL_VALUE = SINGLE_STATUS;
16
17        # This query is not working
18            ALTER TABLE product ADD COLUMN COL_NAME varchar(127) GENERATED ALWAYS AS (COL_VALUE) VIRTUAL;
19
20            SET indx = indx + 1;
21            UNTIL indx = JSON_LENGTH(PRODUCTS)
22       END REPEAT;
23    RETURN COL_NAME;
24END;
25
26SELECT CREATE_COLUMN (001);