postgres:get executable query from query with para

问题:

Is there any way to get executable query from query with $ parameters.Actually its weird but i want to store executable query in database.A complete query without parameters($1,$2,$3)

i am using node-postgres

pg.connect(conString, function(err, client, done) {

console.log('Executing Insert query');

client.query('insert into tablename(column1,column2,column3) values($1,$2,$3)',["data1","data2","data3"],  function(err, result) {

    done();

    console.log('finished executing Insert query');
    });
});

this is what i need

insert into tablename(column1,column2,column3) values("data1","data2","data3")

回答1:

pg-promise has a query formatting engine that can be used independently of queries.

const pgp = require('pg-promise')(/* Initialization Options */);

const query = pgp.as.format('INSERT INTO table(column1,column2,column3) VALUES($1,$2,$3)',
    ["data1", "data2", "data3"]);

console.log(query);

Outputs:

INSERT INTO table(column1,column2,column3) VALUES('data1','data2','data3')

See format API.

UPDATE

For a high-performance approach with a single INSERT query see Multi-row insert with pg-promise.

回答2:

You might want to create a procedure in the PostgreSQL which accepts the query name and arguments as parameters, the procedure can prepare the query you are passing in and execute with the parameters. There is an example of how to do this in MySQL, please find below.

How To have Dynamic SQL in MySQL Stored Procedure

http://dev.mysql.com/doc/refman/5.7/en/sql-syntax-prepared-statements.html