jmealo
commented
7 years ago @ivanceras: Kudos on the project so far. Would you mind fleshing out this ticket a bit? I can't tell if you're trying to send multiple records as a transaction or simply want the endpoint to return that information when applicable on a single operation.
If you're grouping multiple operations (and order doesn't matter):
I usually create a giant CTE and then UNION the records. If the records are unlike I use row_to_json. The order in which the queries are executed isn't deterministic/guaranteed IIRC.
Here are simplistic JavaScript examples of what that looks like:
function queriesToReturningCte(queries) {
var cte = [],
select = [];
queries.forEach(function(query, i) {
query = query.trim();
// Strip trailing semi-colon
if (query.slice(-1) === ';') {
query = query.slice(0, -1);
}
if (query.slice(-11).toLowerCase() !== 'returning *') {
query += ' RETURNING *'
}
cte.push(`q${i} AS (${query})`);
select.push(`SELECT * FROM q${i}`);
});
return `WITH ${cte.join(',\n')} ${select.join('\nUNION ALL\n')};`;
}
function queriesToReturningJsonCte(queries) {
var cte = [],
select = [];
queries.forEach(function(query, i) {
query = query.trim();
// Strip trailing semi-colon
if (query.slice(-1) === ';') {
query = query.slice(0, -1);
}
if (query.slice(-11).toLowerCase() !== 'returning *') {
query += ' RETURNING *'
}
cte.push(`q${i} AS (${query})`);
select.push(`SELECT row_to_json(q${i}) AS json FROM q${i}`);
});
return `WITH ${cte.join(',\n')} ${select.join('\nUNION ALL\n')};`;
}If you introduced an order of operations to apply UPDATE, INSERT and INSERT ON CONFLICT, DELETE you can easily match results to the operations.
This can be somewhat helpful (https://github.com/zhm/pg-query-parser), I'm not sure if you could reverse the error reporting code to reason about any errors coming back.
ivanceras
posting changeset should response