Escaping Special Characters in PostgreSQL Insert Statement

Question

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:

1. quote_literal()
2. 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;

Answer 1

The main problem is, that you are using quote_ident which is for quoting identifiers (column names, table names, ...) where you should be using quote_literal which is used to properly quote literal values.

To declare your variable and assign the value you can use a second level of dollar quoting so you don't need to worry about single quotes inside the value:

declare 
  jscript TEXT := $js$
     /* JScript Comments Here*/
     $(document).ready(function(){
       // Initialize Datatable ...
       $(#Index').Datatable({
       "paging": true, // comment here
       "responsive": true, // comment here 
       "pageLength": 25, // comment here
       "columnDefs": [ {
       ...
       ...
       ...}]
     });
  $js$;

Dynamic SQL is better created using the format() function which takes care of quoting properly:

execute format('Insert into abc.my_table(file, data, gen_flag) values(%L, %L, %L), 'main.js',jscript, 'N')';

---

But you do not need dynamic SQL at all, you can just write:

insert into abc.my_table(file, data, gen_flag) values('main.js', jscript, 'N');

Online demo: https://dbfiddle.uk/?rdbms=postgres_10&fiddle=81b36bb1d3f246637186a419a9b337d4