Invalid operation when creating Stored Procedure

Question

Hi,  
  
I've been trying to create a stored procedure using SQL Workbench/J as well as directly in the Redshift Query Editor and I keep getting errors like "\[Amazon](500310) Invalid operation: unterminated dollar-quoted string at or near "$$ LANGUAGE plpgsql" Position: 1;" from the query editor or "\[Amazon](500310) Invalid operation: Create procedure is not supported for language plpgsql.;" from Workbench/J.  
  
I've read the note on https://docs.aws.amazon.com/redshift/latest/dg/stored-procedure-create.html so I assumed that doing it with Workbench/J wouldn't be a problem (I also made sure to update the JDBC driver to version 1.2.27.1051). To make sure the issue isn't with my procedure, I also tried running example procedures from the documentation and got the same error message.   
  
Anybody else experiencing the same issue and any ideas on how to solve it?

Answer

You're right, the cluster was on version 1.0.7078, I upgraded it and now everything works fine.  
  
Thanks Joe!

Answer

I'm running on an updated Redshift cluster. I receive the same error when using Aginity (4.9.3.2783) or when using SQL Workbench/J (Build 125). SQL Workbench/J  (Build 124) works fine.  
  
Changing the Redshift JDBC driver didn't make a difference for build 125. Build 124 worked fine with RedshiftJDBC42-1.2.16.1027 and RedshiftJDBC42-1.2.10.1009.  
  
Thanks,  
Glenn

Answer

The current version of your Redshift cluster must be 1.0.7287 or later. You will probably be patched to a version with Stored Procedure support in your next maintenance window.  
  
Here is the version info from my cluster in the US West 2 region. It is on 1.0.7152 so it does not have SP support yet.

```
SELECT version();
                                                         version
--------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 8.0.2 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.4.2 20041017 (Red Hat 3.4.2-6.fc3), Redshift 1.0.7152
```

Invalid operation when creating Stored Procedure

関連するコンテンツ