In this post, we will see how you can create Redshift Stored Procedure with example. We will also talk about redshift stored procedure parameters and dynamic sql in stored procedure.
What is a stored procedure ?
Stored Procedure (SP) is a database object which encapsulates business transformation logic into one unit. Stored Procedure can consists of one or many SQL statements primarily DML ones.
SP increases the capability of SQL by allowing programming constructs like Loops, Conditional statements, Variables etc. in it. You can consider SP as something between SQL & programming language like C, Java etc.
Most modern data warehouse solutions provide capability to write Stored Procedures including Redshift. In this post, we will see how you can create a basic Redshift Stored Procedures with all the common expressions in it.
Redshift Stored Procedure example
Redshift Stored Procedures follows a standard template which you can use to write your first SP. This is the empty SP template which will use in this post and will keep on adding more constructs to it.
create or replace procedure public.sp_template() language plpgsql as $_$ --DECLARE variables here begin --MAIN code goes here end; $_$
One detailed sample template is also present at the end of this post for your easy reference.
Execute Stored Procedure in Redshift
You can execute stored procedure by using CALL method followed by Stored Procedure name. Make sure to GRANT proper execute privileges to other users or groups who may execute it. Default privileges assigned to Stored Procedure is of INVOKER level.
call public.sp_template();
Redshift Stored Procedure print statement
You can print any statement in the Redshift Stored procedure by using the RAISE construct. It is similar to “logger” in python. You can RAISE message with level as NOTICE, INFO, LOG, WARNING, EXCEPTION. In this example we have used INFO to print statement in stored procedure.
create or replace procedure public.sp_template() language plpgsql as $_$ begin raise info 'Hello & Welcome to Redshift Stored Procedure Tutorial'; end; $_$
You can chose “%” to represent a dynamic variable while printing a message in RAISE statement.
Redshift Stored Procedure with parameters
Redshift Stored Procedure can take input parameters, output parameters and in-out parameters.
How to pass parameters to Stored Procedure
You can pass the parameters in SP definition between the parentheses after the Stored Procedure name.
We have added “IN ip_name varchar(100), OUT out_city varchar(100)” as the input parameter and output parameter in the Stored Procedure definition.
create or replace procedure public.sp_template(IN ip_name varchar(100),OUT out_city varchar(100)) language plpgsql as $_$ begin raise info 'Hello & Welcome to Redshift Stored Procedure Tutorial'; end; $_$
How to read input parameters in Stored Procedure
You can use ordinal position of parameters to read input parameters. Like $1 for first parameter & $2 for second parameter and so on.
create or replace procedure public.sp_template(IN ip_name varchar(100),OUT out_city varchar(100)) language plpgsql as $_$ begin raise info 'Hello & Welcome to %',$1; end; $_$ call public.sp_template('Redshift Stored Procedure Tutorial');
Create variables in Stored Procedure
You can also use variables for input parameters and assign the values to it. Also you can create variables to do intermediary calculations.
create or replace procedure public.sp_template(IN ip_name varchar(100),OUT out_city varchar(100)) language plpgsql as $_$ declare input_message varchar(100); begin input_message := $1 ; raise info 'Hello & Welcome to %',$1; raise info 'This is variable assignment %',input_message; end; $_$
You can also use ALIAS to assign input parameters to variables directly.
create or replace procedure public.sp_template(IN ip_name varchar(100),OUT out_city varchar(100)) language plpgsql as $_$ declare input_message varchar(100); alias_message alias for $1; begin input_message := $1 ; raise info 'Hello & Welcome to %',$1; raise info 'This is variable assignment %',input_message; raise notice 'This is alias assignment %',alias_message; end; $_$
Let’s assign some value to the output parameter as well in the Stored Procedure.
create or replace procedure public.sp_template(IN ip_name varchar(100),OUT out_city varchar(100)) language plpgsql as $_$ declare input_message varchar(100); alias_message alias for $1; begin input_message := $1 ; raise info 'Hello & Welcome to %',$1; raise info 'This is variable assignment %',input_message; raise notice 'This is alias assignment %',alias_message; out_city := 'PARIS'; raise log 'Setting output parameter to %',out_city; end; $_$
Create dynamic SQL in Stored Procedure
When you are working in Stored Procedure you may have to create dynamic SQL. The dynamic SQL may include SQL Statements along with variables to create legit SQL query.
If you are using variables in dynamic SQL then make sure to enclose it in quote_literal() function. This is done to handle quotes coming as actual values in the variable.
Declare a variable first which will store the query in string format. Once done then run the dynamic SQL using EXECUTE command.
create or replace procedure public.sp_template(IN ip_name varchar(100),OUT out_city varchar(100)) language plpgsql as $_$ declare input_message varchar(100); alias_message alias for $1; ct_sql varchar(1000); dp_sql varchar(1000); begin input_message := $1 ; raise info 'Hello & Welcome to %',$1; raise info 'This is variable assignment %',input_message; raise notice 'This is alias assignment %',alias_message; out_city := 'PARIS'; raise log 'Setting output parameter to %',out_city; dp_sql := 'DROP TABLE IF EXISTS temp_city'; EXECUTE dp_sql; ct_sql := 'CREATE TEMP TABLE temp_city as (SELECT '||quote_literal(input_message)||' as msg,'||quote_literal(out_city)||' as city)'; EXECUTE ct_sql; end; $_$ --call public.sp_template('Redshift Stored Procedure Tutorial') --select * from temp_city;
Note: You can only have 1 SQL statement inside a variable holding dynamic SQL. In this example I cannot keep both DROP/CREATE statement together as part of Dynamic SQL in one variable. It will throw error during execution.
Grant Execute on Stored Procedure
Once you have created Stored Procedure you should grant execute privileges to the required users or groups.
GRANT EXECUTE ON PROCEDURE public.sp_template(IN ip_name varchar(100),OUT out_city varchar(100)) TO GROUP team_group1,user_2;
Check Stored Procedure definition
You can view existing stored procedure definition by using SHOW command
SHOW PROCEDURE public.sp_template(IN ip_name varchar(100),OUT out_city varchar(100));
Remove Stored Procedure
You can remove stored procedure from the Redshift schema by using DROP statement.
DROP PROCEDURE public.sp_template(IN ip_name varchar(100),OUT out_city varchar(100));
Add Conditional Statements in SP
You can easily add conditional statements like IF in the stored procedure. Don’t forget to add END IF to close the IF statement block
create or replace procedure public.sp_template(IN ip_name varchar(100),OUT out_city varchar(100)) language plpgsql as $_$ declare input_message varchar(100); alias_message alias for $1; ct_sql varchar(1000); dp_sql varchar(1000); begin input_message := $1 ; raise info 'Hello & Welcome to %',$1; raise info 'This is variable assignment %',input_message; raise notice 'This is alias assignment %',alias_message; out_city := 'PARIS'; -- conditional statement IF IF $1 IS NOT NULL THEN out_city := 'AMSTERDAM'; END IF; raise log 'Setting output parameter to %',out_city; dp_sql := 'DROP TABLE IF EXISTS temp_city'; EXECUTE dp_sql; ct_sql := 'CREATE TEMP TABLE temp_city as (SELECT '||quote_literal(input_message)||' as msg,'||quote_literal(out_city)||' as city)'; EXECUTE ct_sql; end; $_$
Add Loops in SP
In Redshift Stored Procedure you can add loops using LOOP keyword along with FOR keyword.
create or replace procedure public.sp_template(IN ip_name varchar(100),OUT out_city varchar(100)) language plpgsql as $_$ declare input_message varchar(100); alias_message alias for $1; ct_sql varchar(1000); dp_sql varchar(1000); -- loop variable i int; begin input_message := $1 ; raise info 'Hello & Welcome to %',$1; raise info 'This is variable assignment %',input_message; raise notice 'This is alias assignment %',alias_message; out_city := 'PARIS'; IF $1 IS NOT NULL THEN out_city := 'AMSTERDAM'; END IF; --loop statement FOR i IN 1..5 LOOP out_city := out_city ||'+'|| i ||' '; END LOOP; raise log 'Setting output parameter to %',out_city; dp_sql := 'DROP TABLE IF EXISTS temp_city'; EXECUTE dp_sql; ct_sql := 'CREATE TEMP TABLE temp_city as (SELECT '||quote_literal(input_message)||' as msg,'||quote_literal(out_city)||' as city)'; EXECUTE ct_sql; end; $_$
I think this information is sufficient for anyone who is starting with Stored Procedure in Redshift. Some of the advance topics will be covered in next post.
- cursors
- exception handling
- system table/views for debugging
Leave a Reply