Execute multiline query with Kubectl + exec + PSQL

Question

I'm trying to run a query with Kubectl , as follows:

kubectl -n employeesns exec -ti employeedpoddb-0 -- psql -d db_people -U postgres 
-c 'create extension if not exists dblink;' 
-c 'SELECT dbemployees."empId" , dbemployees."createdAt" , dbemployees."updatedAt"
from "users" as "dbemployees"
WHERE dbemployees."empId" not in (
    SELECT "empId"
    FROM   dblink('dbname=peopledb','SELECT "empId"  FROM employees')
    AS     dbpeople("empId" varchar)
)'

However I get

ERROR:  syntax error at or near "SELECT"
LINE 1: ...SELECT "empId" FROM   dblink(dbname=peopledb,SELECT
                                                                 ^
command terminated with exit code 1

How can we execute multiline SQL query with Kubectl ?

Answer 1

It's because your inner ' is not escaped; you'll see the same thing locally

$ echo 'hello 'world' from shell'

you just need to escape those inner quotes, or change the outer to " and then escape those usages, based on your needs

-c 'SELECT dbemployees."empId" , dbemployees."createdAt" , dbemployees."updatedAt"
from "users" as "dbemployees"
WHERE dbemployees."empId" not in (
    SELECT "empId"
    FROM   dblink('\''dbname=peopledb'\'','\''SELECT "empId"  FROM employees'\'')
    AS     dbpeople("empId" varchar)
)'