First time posting a question here, so please excuse me if there are formatting issues.
I am trying to write a Netezza Stored Procedure to dynamically update a filename as it goes through a loop. However, when trying to define the folder path as a string, the backslash '' is escaping the single quote terminating the string.
...
OUTPUT_FILEPATH = 'C:UsersTest'
...
ERROR [01000] NOTICE: plpgsql: ERROR during compile of XXX.TEST near line 1
ERROR [HY000] ERROR: unterminated string starting on line 1
If I tried using double backslash to show the escapechar, both backslash characters will appear.
...
OUTPUT_FILEPATH = 'C:UsersTest\'
...
NOTICE: Creating Batch TEST-B 01 at C:UsersTest\
If I tried to repeat the single quotes once or twice, it either doesn't compile, or repeats the same error that the string is unterminated.
...
OUTPUT_FILEPATH = 'C:UsersTest''
...
ERROR [42000] Syntax error or access violation
...
OUTPUT_FILEPATH = 'C:UsersTest'''
...
ERROR [01000] NOTICE: plpgsql: ERROR during compile of XXX.TEST near line 1
ERROR [HY000] ERROR: unterminated string starting on line 1
How do we prevent the escapechar from escaping the terminating single quote in NZPLSQL?
Full script reproduced below for reference:
...
CREATE OR REPLACE PROCEDURE XXX.TEST()
RETURNS INTEGER
LANGUAGE NZPLSQL AS
BEGIN PROC
DECLARE
FILE_HDR NVARCHAR;
BATCH_ID NVARCHAR;
OUTPUT_FILEPATH NVARCHAR;
CNT INT;
BEGIN
CNT:=1;
FILE_HEADER:='TEST-B';
WHILE CNT < 10 LOOP
BATCH_ID:= TO_CHAR(CNT, 'FM09');
OUTPUT_FILEPATH:= 'C:UsersTest'||FILE_HEADER||BATCH_ID||'.csv';
RAISE NOTICE 'Creating Batch % % at %', FILE_HEADER, BATCH_ID, OUTPUT_FILEPATH;
END LOOP;
END;
END_PROC;
...
