I am writing a function that requires me to store some JavaScript code in a PostgreSQL database table (this is required). I am looking for a "Lazy" way of doing this without modifying my PostgreSQL insert statement to escape the special characters at every instance it might occur within the JavaScript code. I primarily want to avoid doing the escapes in the event that the JavaScript code were to get lengthier. Since this might get a bit messy quickly. PostgreSQL seems to offer the following functions:
- quote_literal()
- quote_ident()
Reference: PostgreSQL String Formatting Functions
Having tested both of these a common error I am running into is the following error:
Error: unterminated quoted identifier at or near "": true
At a quick glance it appears that my issue lies in the formatted JavaScript text itself.
Is there a "Lazy" way for me to avoid escaping all these special characters without having to do this manually? Essentially, I would like to dump this code into a variable and perform the insert using the stored variable without (ideally) or with minimal modifications to the stored JScript text.
Below is an example of what my code looks like:
CREATE OR REPLACE FUNCTION abc.my_function(text, text, text, text, text[])
RETURNS void AS $body$
DECLARE
-- Variable Declarations here
jscript TEXT := quote_ident('/* JScript Comments Here*/ $(document).ready(function(){
// Initialize Datatable ...
$('#Index').Datatable({
"paging": true, // comment here
"responsive": true, // comment here
"pageLength": 25, // comment here
"columnDefs": [ {
...
...
...
}]
});
');
BEGIN
...
...
...
-- Insert static HTML
execute 'Insert into abc.my_table(file, data, gen_flag) values('||'''main.js||','||jscript||','|| '''N''' || ')';
...
...
...
END;
$body$
LANGUAGE 'plpgsql' VOLATILE;
