Passing variable inside stored procedure in Redshift returning error-CodePudding

I have this stored procedure that I can successfully compile, but upon calling the procedure, I get an error:

Amazon Invalid operation: syntax error at or near "$1"

Can you please help me understand what is wrong? Thank you in advance.

 CREATE OR REPLACE PROCEDURE SchemaName.Testing_Passing_Parameter_in_SP ()
 LANGUAGE plpgsql 
 AS   
 $$ 
  
  DECLARE 
  FirstDayofWeek VARCHAR(50); 
  Unload_Query VARCHAR(65000); 

  BEGIN 

WITH CTE_FirstDayOfWeek AS (SELECT TO_CHAR(DATE_TRUNC('WEEK', GETDATE()), 'MMDDYYYY') AS FirstDayOfWeek) 
SELECT FirstDayOfWeek INTO FirstDayofWeek FROM CTE_FirstDayOfWeek; 

  Unload_Query := ' UNLOAD (''' || 'SELECT  *
                                   FROM SchemaName.TableName '
                                ||             
                           ''') 
                   TO  '''||'s3://BucketName/FolderName/Sample_File_'||FirstDayofWeek||'.csv'||''' 
                   CREDENTIALS ''' ||'aws_access_key_id=xxx'||';'||'aws_secret_access_key=xxx'||  '''
                   Header
                   ALLOWOVERWRITE
                   delimiter '''||','||'''
                   addquotes
                   PARALLEL OFF          
                          ';
  EXECUTE Unload_Query;

 END; 
 $$;

CALL SchemaName.Testing_Passing_Parameter_in_SP ();

CodePudding user response：

Redshift is based on very old Postgres, where is not detection of collision between PL/pgSQL variables and SQL identifiers. The variable FirstDayofWeek has same name like column FirstDayofWeek. The general solution is using prefix _ for PL/pgSQL variables:

DECLARE 
  _FirstDayofWeek VARCHAR(50); 
  _Unload_Query VARCHAR(65000);