Redshift Stored Proc Examples

Code Snippet from slides

	#https://d1.awsstatic.com/events/reinvent/2019/REPEAT_1_Stored_procedures_in_Amazon_Redshift_ANT337-R1.pdf
	#Temp Table
	CREATE PROCEDURE sp_result
	AS $$
	BEGIN
	DROP TABLE IF EXISTS my_temp;
	CREATE TEMP TABLE my_temp AS
	SELECT *
	FROM my_table
	LIMIT 100;
	END;
	$$ LANGUAGE plpgsql;
	CALL sp_result();
	-- Not bound to transaction scope
	SELECT * FROM my_temp;
	#DynamicSQL Example
	CREATE PROCEDURE sp_dynamic(loops INT, tbl_name VARCHAR)
	AS $$
	DECLARE
	i_loop INT:=0;
	BEGIN
	EXECUTE 'CREATE TEMP TABLE '||quote_ident(tbl_name)
	||' (loop INT, loop_ts TIMESTAMP);';
	WHILE i_loop < loops
	LOOP
	i_loop = i_loop + 1;
	RAISE INFO 'Loop pass: %', i_loop;
	EXECUTE 'INSERT INTO '||quote_ident(tbl_name)
	||' SELECT '||i_loop||', '''||SYSDATE||''';';
	END LOOP;
	END;
	$$ LANGUAGE plpgsql;
	BEGIN;
	CALL sp_dynamic(15,'tmp_dynamic');
	SELECT * FROM tmp_dynamic;
	END;
	#Debugging
	#Looping
	CREATE PROCEDURE sp_debug(loops INT)
	AS $$
	DECLARE
	i_loop INT:=0;
	BEGIN
	RAISE INFO 'Starting - % loops',loops;
	WHILE i_loop < loops
	LOOP
	i_loop = i_loop + 1;
	RAISE INFO 'Loop pass - %', i_loop;
	END LOOP;
	RAISE INFO 'Finished - % loops', i_loop;
	END;
	$$ LANGUAGE plpgsql;
	BEGIN; CALL sp_debug(10); END;

view raw Redshiftexamples.sql hosted with ❤ by GitHub

Keep Exploring!!!