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

Question

Following the AWS documentation to create a stored procedure via the Redshift Query Editor or SQL Workbench/J gives this error: Amazon Invalid operation: unterminated dollar-quoted string at or near "$$. Here is a generic sample of code I was attempting:

CREATE OR REPLACE PROCEDURE
load_inventory () AS $$
BEGIN 
INSERT INTO inventory(sku,qty,location) 
SELECT sku, qty, 'location name' FROM location_inventory; 
END; 
$$
LANGUAGE plpgsql;

How should the statement be written to successfully create a stored procedure in Redshift?

Answer 1

Using single-quotes instead of a dollar-quoted string solved the issue. Note that the string used within the SELECT statement has repeated single quotes.

CREATE OR REPLACE PROCEDURE
load_inventory () AS '
BEGIN 
INSERT INTO inventory(sku,qty,location) 
SELECT sku, qty, ''location name'' FROM location_inventory; 
END; 
'
LANGUAGE plpgsql;