AMAZON 500310: Invalid operation: syntax error at or near " Drop"

Question

I followed https://dwgeek.com/redshift-stored-procedure-return-result-set-working-example.html/ step of creating a stored procedure using a temporary table but facing the error above.

CREATE OR REPLACE PROCEDURE sample_return_table(tmp_table INOUT varchar(256)) 
AS '
  DECLARE
    row record;
  BEGIN
   EXECUTE ' DROP table if exists ' || tmp_table;
   EXECUTE ' CREATE temp TABLE  ' || tmp_table ||  ' AS SELECT DISTINCT table_schema FROM information_schema.tables ';
  END;
'
LANGUAGE plpgsql;

Error:

[Amazon](500310) Invalid operation: syntax error at or near "DROP" 
Position: 132; [SQL State=42601, DB Errorcode=500310]
1 statement failed.

May I know what is the issue ?

Answer 1

Here is a sample Stored Procedure from Overview of stored procedures in Amazon Redshift:

CREATE OR REPLACE PROCEDURE test_sp1(f1 int, f2 varchar)
AS $$
BEGIN
  RAISE INFO 'f1 = %, f2 = %', f1, f2;
END;
$$ LANGUAGE plpgsql;

Notice that use of $$ to identify the content of the procedure, whereas your code is using '.

The problem with using ' is that it is also being used inside the procedure (eg just before DROP) and is therefore confusing Redshift.

Try changing to an unused sequence (such as $$) to avoid this problem.