1

Following code on command line

select 
   $$
   Line 1
   Line 2
   Line '3'
   $$

But when I try to use $$ delimited string for setting a variable in stored proc , it does not work I get error [Amazon](500310) Invalid operation: syntax error at or near "Line"

CREATE OR REPLACE PROCEDURE testMultiString()
   LANGUAGE plpgsql AS
    $$
    DECLARE 
       endtime datetime;
       sqlText text; 
    
    BEGIN
      SELECT INTO sqlText  
            $$
       Line 1
       Line 2
       Line '3'
       $$;
      RAISE INFO 'sql  : %', sqlText;
      
    END
    $$;
John Rotenstein
  • 241,921
  • 22
  • 380
  • 470
user3376169
  • 405
  • 1
  • 5
  • 17

1 Answers1

3

I don't know Redshift, but in Postgres you need to use a different "dollar quote" string when you nest them, e.g.

  SELECT INTO sqlText  
        $text$
   Line 1
   Line 2
   Line '3'
   $text$;

Although a simple assignment would be better

sqltext := $text$
   Line 1
   Line 2
   Line '3'
   $text$;

Or use a different delimiter on the outer level e.g. $body$, then you can use $$ on the inside.