Generating SQL Scripts on SQL Workbench/J to Create Amazon Redshift Stored Procedures

Amazon Redshift logo

The task of generating a SQL script for a stored procedure or function is fairly simple in Microsoft SQL Server using SQL Server Management Studio, or in Oracle Database, using SQL Developer.

However, if your database is Amazon Redshift, you’re probably using SQL Workbench/J to interact with your database, and unfortunately, its ability to script out stored procs is limited.

I recently discovered that, when selecting the stored procedure from a list in Database Explorer, the SQL script that is supposed to be able to re-create the stored proc does not work. It looks something like this:

DROP PROCEDURE schemaname.procname()
/

CREATE FUNCTION procname (null)
RETURNS -
VOLATILE
AS $$

	
	
begin

.... << stored proc code here >> ...

 end;
$$ LANGUAGE plpgsql
;

You might notice a few things about this code. First of all, this stored procedure is being called a function here rather than a procedure. Since it is, in reality, a stored procedure, it returns no value, but this script doesn’t seem to know how to do that. Also there happen to be no parameters for this stored proc, but the scripting engine puts “null” as the parameter data type rather than leaving empty parentheses. The code for this stored proc should be this:

CREATE OR REPLACE PROCEDURE schemaname.procname ()
AS $$

	
	
begin

.... << stored proc code here >> ...

 end;
$$ LANGUAGE plpgsql
;
/

I have, with a little help from the Amazon Redshift documentation and the PostgreSQL documentation, been able to write a script that will correctly script out your stored proc, with the exception of the datatypes for the parameters, if there are any. If there are any you will need to look at the pg_type table and the proargtype column in the pg_proc_info table.

 SELECT 'CREATE OR REPLACE ' || 
  CASE WHEN p.prokind='p' THEN 'PROCEDURE ' 
       WHEN p.prokind='f' THEN 'FUNCTION ' 
       ELSE '???? ' END ||
  n.nspname || '.' || proname || ' (' || 
  CASE WHEN p.proargtypes IS NULL OR p.proargtypes='' THEN ''
  ELSE ' ???? '
  END || 
  ')' ||  CHR(13) ||
  CASE WHEN p.prorettype=0 THEN ''  
       ELSE 'RETURNS ' || (SELECT TOP 1 typname FROM pg_type WHERE oid=p.prorettype) || CHR(13) END || 
  CASE WHEN p.prokind='p' THEN ''
       WHEN p.provolatile='i' THEN 'IMMUTABLE' 
       WHEN p.provolatile='s' THEN 'STABLE'  
       WHEN p.provolatile='v' THEN 'VOLATILE' END || 
  CHR(13) || 'AS $$' || CHR(13) || CHR(13) ||
  p.prosrc ||
  '$$ LANGUAGE plpgsql' || CHR(13) || ';' || CHR(13) || '/'
  FROM
    pg_catalog.pg_namespace n
JOIN pg_catalog.pg_proc_info p ON
    p.pronamespace = n.oid
JOIN pg_user b ON
    b.usesysid = p.proowner
WHERE
    n.nspname not in ('information_schema','pg_catalog') 
    and p.proname like '%whatever%;  --put a part or all of the function/proc name here with percent wildcards and remember that like is case-sensitive. Use ilike for case-insensitive searching.

The result should be a single column with the text for a script to re-create your stored procedure or function!

Happy New Year!

A book I’d purchased some time back, but just finished reading recently, Show Your Work!: 10 Ways to Share Your Creativity and Get Discovered, by Austin Kleon, is an easy read, but well worth it.

cover of Show Your Work

Regardless of what field one works in, whether it be technology, the arts, or any other field really, the advice given in this book can help to make one realize the value they add to their field, and also how to showcase it to others.

For those of you who have resolved that it’s a “new year, new you”, I highly recommend reading this book now, rather than later.

Microsoft Learn for the Win!

design from the Microsoft Lean website

Over the last ten years or so, I have worked with many different computer-based training (CBT) programs from various vendors, some paid, and some free. Of all the ones I’ve used, Microsoft Learn – which is free, as in “free beer”! – has been the best overall.

Not only is the variety of subjects available for study more diverse than the other sites I’ve used (though the Learn classes are all for only Microsoft-based technologies, as far as I know), but the quality of the lessons and the degree for which they can prepare one for at least some certifications is comparable with a paid class or set of Microsoft Press books.

I was particularly happy with the Power BI and Azure training that I took, and I plan to move on to some AI and Machine Learning courses this next year.

%d bloggers like this: