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 $$

DECLARE

min_val int;

BEGIN

DROP TABLE IF EXISTS tmp_tbl;

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;

END;

$$ LANGUAGE plpgsql;

Code

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 $$

DECLARE

out_user alias for $1;

BEGIN

SELECT INTO out_user current_user;

END;

$$ LANGUAGE plpgsql;

Code

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();

Code

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.