SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software Amazon Web Services AWS Tutorials and Guides
Development resources, articles, tutorials, code samples and tools and downloads for ASP.Net, SQL Server, R Script, Windows, Windows Phone, AWS, SAP HANA and ABAP, like SAP UI5, Screen Personas, etc.

Stored Procedures on Amazon Redshift: unterminated dollar-quoted string at or near "$$

Amazon Redshift support stored procedures since May 2019 which enbled data warehouse SQL developers to build batches of SQL codes and call repeatitively using PL/pgSQL (Procedural Language/PostgreSQL). Although Amazon Redshift enables creation of stored procedures using PL/pgSQL if you create your first procedure you might experience unterminated dollar-quoted string at or near "$$ error.

As a database programmer working with stored procedures on SQL Server databases, I was very eager to create my first stored procedure on Amazon Redshift database when I heard the announcement of stored procedure support. I copied the sample stored procedure

CREATE OR REPLACE PROCEDURE test_sp1(f1 int, f2 varchar(20))
AS $$
min_val int;
CREATE TEMP TABLE tmp_tbl(id int);
INSERT INTO tmp_tbl values (f1),(10001),(10002);
SELECT INTO min_val MIN(id) FROM tmp_tbl;
RAISE INFO 'min_val = %, f2 = %', min_val, f2;
$$ LANGUAGE plpgsql;

Unfortunately I experienced following error message when I execute CREATE PROCEDURE command on SQL Workbench/J SQL editor.

An error occurred when executing the SQL command:
[Amazon](500310) Invalid operation: unterminated dollar-quoted string at or near "$$

[Amazon](500310) Invalid operation: unterminated dollar-quoted string at or near $$

Then I execute the same CREATE OR REPLACE PROCEDURE statement directly on Amazon Redshift database using its native development client Query Editor on Amazon Redshift Dashboard, the stored procedure is successfully created without any error message.

create procedure on Amazon Redshift database using Query Editor

I realized that I was using SQL Workbench/J Build 125 whih was released on 8th of May, 2019 and Amazon Redshift Stored Procedure support was announced on 17th of May. So the root cause of error message : "[Amazon](500310) Invalid operation: unterminated dollar-quoted string at or near "$$" was the SQL client tool which is not yet supporting newest features of Amazon Redshift.

Amazon Redshift SQL developers can create test their first stored procedure on Redshift data warehouse by executing below simple code

CREATE OR REPLACE PROCEDURE firstRedshiftStoredProcedure( OUT varchar(100) )
AS $$
out_user alias for $1;
SELECT INTO out_user current_user;
$$ LANGUAGE plpgsql;

And if you are successful to create stored procedure on Amazon Redshift for the first time, you can use that SQL client for PL/pgSQL code developments on Amazon Redshift.

SQL programmers can execute their first stored procedure executing following code

call firstRedshiftStoredProcedure();

Other Amazon Redshift client tool DataRow has already support creating stored procedures on a Redshift database. I can suggest Redshift SQL developers to work on this SQL client for their Amazon Redshift data warehouse platform.

AWS Tutorials

AWS Tutorials


Copyright © 2004 - 2020 Eralper YILMAZ. All rights reserved.
Community Server by Telligent Systems