Using SQLWorkBench for Redshift Stored Procedures
When writing Redshift stored procedures pl/pgsql, it appears sqlworkbench/j does not return any type of “raise” statement to the user within SQLWorkBench which makes it very hard to debug… Thank fully, there is a workaround.
That is: you can query the redshift view: SVL_STORED_PROC_MESSAGES. Which does record all raise statements for a period of time. Superuser will be able to see all, and normal users see their own.
select * from SVL_STORED_PROC_MESSAGES order by recordtime desc;
Note: For most versions of SQLWorkBench you have to use -> Tools -> Options -> SQL Execution -> Alternative Delimiter: / to compile pl/pgsql.
And yes while that alternative delimiter is active, you have to terminate all SQL and pl/pgsql with it “/”.
Another important feature of SQLWorkBench that I came across while testing my stored procedures with SQLWorkBench is the File -> New Window function which allow me to open several sessions with SQLWorkbench with different users connected.