MariaDB extract a column for a variable of Type ROW dynamically

Question

delimiter $$

CREATE OR REPLACE PROCEDURE  `populate_audit_helper`(
    IN in_db_name VARCHAR(100),
    IN in_tbl_name VARCHAR(100),
    IN in_row_id INT(10)
)
BEGIN
    DROP TEMPORARY TABLE IF EXISTS t_agents;
    SET @SQL := CONCAT('CREATE TEMPORARY TABLE t_agents AS SELECT * FROM `', in_tbl_name, '` WHERE row_id = ', in_row_id);
    PREPARE stmt FROM @SQL;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

    BEGIN
        DECLARE c_data CURSOR FOR SELECT * FROM t_agents;
        BEGIN
            DECLARE rec ROW TYPE OF c_data;
            DECLARE done INT DEFAULT FALSE;
            DECLARE col CHAR(40);
            DECLARE val TEXT(10000);

            DECLARE c_columns CURSOR FOR 
                SELECT column_name
                FROM INFORMATION_SCHEMA.COLUMNS
                WHERE TABLE_SCHEMA = in_db_name
                    AND table_name = in_tbl_name
                ORDER BY ordinal_position;

            DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

            OPEN c_columns;
            OPEN c_data;

            FETCH c_data INTO rec;          

            SET @create_list := "";

            read_loop: LOOP
                FETCH c_columns INTO col;

                IF done THEN
                    LEAVE read_loop;
                END IF;

                -- SELECT rec.col; -- <<====

                SET @create_list := CONCAT(@create_list, "'", col, "', '", rec.name, "', ");

            END LOOP read_loop;

            SET @SQL := CONCAT ("INSERT INTO x1 SET dynamic_cols_l1 = COLUMN_CREATE(", TRIM(TRAILING ", " FROM @create_list), ")");
            PREPARE stmt FROM @SQL;
            EXECUTE stmt;
            DEALLOCATE PREPARE stmt;

            CLOSE c_columns;
            CLOSE c_data;
        END;
    END;
 END$$

In the above procedure, i am looping through all the columns in a table and i am planning to build a query to populate a table with dynamic column.

Is there any way to substitute the value of col in the line highlighted in above procedure?

Eg. the value stored in col is 'name'. So the output required is SELECT rec.name

Update

Somehow i managed to write a dirty solution without the use of ROW TYPE and with single cursor. It works so, so far i did not find any problem. Pls guide me if there is any better way.

delimiter $$

CREATE OR REPLACE PROCEDURE  `populate_audit_helper_new3`(
    IN in_db_name VARCHAR(100),
    IN in_tbl_name VARCHAR(100),
    IN in_row_id INT(10)
)
proc: BEGIN
        DECLARE done INT DEFAULT FALSE;
        DECLARE col CHAR(40);
        DECLARE val TEXT(10000);
        DECLARE q TEXT(100000);     

        DECLARE c_columns CURSOR FOR 
            SELECT column_name
            FROM INFORMATION_SCHEMA.COLUMNS
            WHERE TABLE_SCHEMA = in_db_name
                AND table_name = in_tbl_name
            ORDER BY ordinal_position;

        DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

        OPEN c_columns;

        SET q = "";
        SET @val = "";

        read_loop: LOOP
            FETCH c_columns INTO col;

            IF done THEN
                LEAVE read_loop;
            END IF;
            SET q = CONCAT(q, "'", col, "','|', IFNULL(QUOTE(", col, "), ''),'$'");

        END LOOP read_loop;

        SET q = TRIM(TRAILING "," FROM q);

        SET q = CONCAT("CONCAT(", q, ") INTO @val");

        EXECUTE IMMEDIATE CONCAT("SELECT ", q , " FROM ", in_tbl_name, " WHERE row_id = ", in_row_id);

        IF @val = '' THEN
            LEAVE proc;
        END IF;

        SELECT CONCAT("'", REPLACE(@val, "|", "',"), "'") INTO @val;

        SELECT REPLACE(@val, "$", ",") INTO @val;

        SELECT TRIM(TRAILING ",'" FROM @val) INTO @val;

        EXECUTE IMMEDIATE CONCAT("INSERT INTO x1 SET dynamic_cols_l1 = COLUMN_CREATE(", @val, ")" );

        CLOSE c_columns;
 END$$

Answer 1

@alexey Thanks for your support. I tweaked your answer a bit to add support for null and escape character. Below is the final procedure

delimiter $$

CREATE OR REPLACE PROCEDURE  `populate_audit_helper_new5`(
    IN in_db_name VARCHAR(100),
    IN in_tbl_name VARCHAR(100),
    IN in_row_id INT(10)
)
proc: BEGIN

    DECLARE done INT DEFAULT FALSE;
    DECLARE create_list TEXT(10000);
    DECLARE col TEXT(10000);

    DECLARE c_columns CURSOR FOR 
        SELECT column_name
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE TABLE_SCHEMA = in_db_name
            AND table_name = in_tbl_name
        ORDER BY ordinal_position;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN c_columns;

    set @SQL := 'SELECT CONCAT(\'';

    read_loop: LOOP
        FETCH c_columns INTO col;
        IF done THEN
           LEAVE read_loop;
        END IF;
        SET @SQL := CONCAT(@SQL, "\\'", col, "\\'\', \', \', IFNULL(QUOTE(", col, "), \'\'), '\, ");
    END LOOP read_loop;

    set @SQL := substring(@SQL, 1, CHAR_LENGTH(@SQL) - 5);

    set @SQL := concat(@SQL, ') into @create_list FROM `', in_tbl_name, '` WHERE row_id = ', in_row_id);

    PREPARE stmt FROM @SQL;
    execute stmt;
    DEALLOCATE PREPARE stmt;

    EXECUTE IMMEDIATE CONCAT("INSERT INTO x1 SET dynamic_cols_l1 = COLUMN_CREATE(", @create_list, ")" );

    CLOSE c_columns;

END$$

Answer 2

As far as I've seen in mariadb documentation, you can't do it directly. There's no loop support for the ROW TYPE also. However what I would suggest to do is to leave only INFORMATION_SCHEMA.COLUMNS cursor and generate dynamic selects like

 read_loop: LOOP
    FETCH c_columns INTO col;
    IF done THEN
       LEAVE read_loop;
    END IF;
    SET @SQL := CONCAT('SELECT ', col, ' into @colval FROM `', in_tbl_name, '` WHERE row_id = ', in_row_id);
    PREPARE stmt FROM @SQL;
    execute stmt using col;
    DEALLOCATE PREPARE stmt;
    SET @create_list := CONCAT(@create_list, "'", col, "', '", @colval, "', ");

END LOOP read_loop;

As you're splitting only one table row it will be fine enough.

UPDATE: with additional info it can be done like that. After rethinking it:

 set @SQL := 'SELECT CONCAT(';
 read_loop: LOOP
    FETCH c_columns INTO col;
    IF done THEN
       LEAVE read_loop;
    END IF;
    SET @SQL := CONCAT(@SQL, "'\\'", col, "'\\', \\''", col, "'\\', '");

END LOOP read_loop;
set @SQL := substring(@SQL, 1, CHAR_LENGTH(@SQL) - 3);
set @SQL := concat(@SQL, ') into @create_list FROM `', in_tbl_name, '` WHERE row_id = ', in_row_id);
PREPARE stmt FROM @SQL;
execute stmt using col;
DEALLOCATE PREPARE stmt;