Inserting a SQL statement as a record value into a PostgreSQL column

I work with an application that uses PostgreSQL 10.7. Part of the application allows you to bundle a group of of database objects and SQL statements into a package that you can later run when creating a Dev environment.

Each object and SQL statement has its own record in the database. I have to create over 1000 records so I am trying to create a script that will insert the SQL statements into the database for me.

I created my script but I am getting an error once Postgres sees the second “Value” command that is part of the record I am trying to insert.

Here is an example of what I am trying to do:

````insert into MY_TABLE 
   ( NAME, 
     SQL_STMT, 
     ADDED_BY, 
     DATE_ADDED  ) 
values 
   ( 'package_1', 
     'INSERT INTO TABLE_1(NAME, OBJECT_ID, ORDER_NUMBER) VALUES 
  'LCMSMS','PEST',1);'
     'CJONES', 
     '9/11/2019' );````

I am expecting it to be inserted but I am getting the following error. Can anyone guide me on how to “insert my insert statement”?

LINE 8: ...NAME,SQL_STMT,ADDED_BY,DATE_ADDED) VALUES ('LCMSMS...````

Answer

Your SQL statement contains emmbedded quotes, that clash with the surrounding quotes. You would need to double these quotes, like:

````insert into MY_TABLE 
   ( NAME, 
     SQL_STMT, 
     ADDED_BY, 
     DATE_ADDED  ) 
values 
   ( 'package_1', 
     'INSERT INTO TABLE_1(NAME, OBJECT_ID, ORDER_NUMBER) VALUES (''LCMSMS'', ''PEST'', 1);'
     'CJONES', 
     '9/11/2019' );````

---

As commented by mu is too short, another solution would be to use Postgres dollar quoting syntax. This saves you the effort of double quoting each and every embedded quote:

````insert into MY_TABLE 
   ( NAME, 
     SQL_STMT, 
     ADDED_BY, 
     DATE_ADDED  ) 
values 
   ( 'package_1', 
     $$INSERT INTO TABLE_1(NAME, OBJECT_ID, ORDER_NUMBER) VALUES ('LCMSMS', 'PEST', 1);$$
     'CJONES', 
     '9/11/2019' );````