While working with a client, we had a requirement to perform bulk insert/update using Retool table and Redshift database. For this situation, We had to loop through the table records and execute a dynamic SQL query in Redshift.
Redshift database supports execution of dynamic SQL with the help of Prepared Statements or Stored Procedures.
We use prepared statements when we want to execute dynamic SQL queries directly without a stored procedure. When a prepared statement is executed, the SQL statement is parsed, rewritten, and planned. We then EXECUTE the prepared statement.
PREPARE plan_name [ (datatype [, ...] ) ] AS statement
PREPARE prep_select_employee (int) AS select * from employee where empId = $1; EXECUTE prep_select_employee (1001); DEALLOCATE prep_select_employee;
We execute dynamic SQL query in stored procedure using EXECUTE statement. When working with dynamic SQL, we have to handle single-quotes.
EXECUTE command-string [ INTO target ];
CREATE PROCEDURE delete_record(id_value INOUT VARCHAR, table_name INOUT VARCHAR) LANGUAGE plpgsql AS $$ DECLARE BEGIN EXECUTE 'DELETE FROM ' || table_name || ' WHERE id = ' || quote_literal(id_value); END; $$;