How do I send in parameters in COPY TO-query in no

问题:

Im doing a query to export results to CSV in postgres running on a ubuntu-server.

The query gets called from a Node-API which is connecting to Postgres via the node-postgres-driver(https://node-postgres.com/).

The query looks like this:

var sqlText = 
   'Copy (SELECT company.name AS Company, SUM(sale.numbersold) AS NumberSold 
    FROM SALE LEFT JOIN company ON company.companyid = sale.companyid 
    WHERE sale.createdate BETWEEN $1 AND $2 GROUP BY company.name) 
    To \'/tmp/test.csv\' With CSV HEADER DELIMITER \',\'';

Whenever I try to use this query with parameters I get a exception that postgres expected 0 parameters and got 2. How do you send in parameters to a query like this?

If I query with static text like this, it works:

var sqlText = 'Copy (SELECT company.name AS Company, SUM(sale.numbersold) AS NumberSold 
FROM SALE LEFT JOIN company ON company.companyid = sale.companyid 
WHERE sale.createdate BETWEEN \'2018-01-01\' AND \'2018-01-01\' 
GROUP BY company.name) 
To \'/tmp/test.csv\' With CSV HEADER DELIMITER \',\'';

Code in Node:

var params = ['2018-01-01', '2018-01-28']

 let dbResult;
            try {
                    dbResult = await GetStuff(sqlText, params);
            } catch (err) {

            }

回答1:

COPY is an utility statement, and as such doesn't support parametrized execution.

Either the parameters should be injected client-side, or, if you prefer not to, injected server-side with dynamic SQL implemented in a function.

An example is shown in Use function variable in dynamic COPY statement.