ETL SQL

Learn Extract Transform Load using SQL & PySpark

Redshift Stored Procedure for beginners

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

Your email address will not be published. Required fields are marked *