如何在Node.js中使用pg库编写多行结构化长SQL语句？

Got it, let's fix both your issues step by step!

First, let's squash the syntax error: You’ve got an extra + right after the last CASE statement (before AS DECIMAL). That’s what’s throwing the database off—remove that and the core query structure becomes valid.

Next, to fix the readability problem without breaking functionality, you can use ES6 template literals (backticks `) in Node.js. This lets you split your SQL into multiple, properly formatted lines without messing up string concatenation or parameter binding.

Here’s the cleaned-up, multi-line version of your code, with both issues resolved:

async function calculateFhcCoverageRate(id) {
  const client = await pool.connect();
  try {
    const res = await client.query(`
      SELECT 
        CAST(
          (
            CASE WHEN coalesce(hh_total_fhc_0, 0) >= 1 THEN 1 ELSE 0 END +
            CASE WHEN coalesce(hh_total_fhc_1, 0) >= 1 THEN 1 ELSE 0 END +
            CASE WHEN coalesce(hh_total_fhc_2, 0) >= 1 THEN 1 ELSE 0 END +
            CASE WHEN coalesce(hh_total_fhc_3, 0) >= 1 THEN 1 ELSE 0 END +
            CASE WHEN coalesce(hh_total_fhc_4, 0) >= 1 THEN 1 ELSE 0 END
          ) AS DECIMAL
        ) / hh_match_count * 100 AS fhc_coverage_rate
      FROM fixtures 
      WHERE id = ($1)
    `, [id]);
    
    return res.rows[0]; // Return the calculated result for easy use
  } finally {
    client.release();
  }
}

// Example usage
calculateFhcCoverageRate(456)
  .then(result => console.log("FHC Coverage Rate:", result))
  .catch(e => console.error("Error calculating rate:", e.stack));

Key improvements here: #

• Syntax error fixed: Removed the trailing + after the final CASE statement.
• Readable multi-line format: Template literals let you split the query into logical blocks (select clause, cast wrapper, individual CASE checks, from/where clauses) so you can scan and edit parts of the query at a glance.
• Clear column alias: Added AS fhc_coverage_rate to the calculated value, making it easier to reference the result later.
• Cleaner function structure: Separated the async logic into a dedicated function instead of an IIFE nested inside another function, making maintenance simpler.

Important note: Even with template literals, we’re still using parameterized queries (($1) paired with [id]), so you don’t lose protection against SQL injection—this keeps your code secure while boosting readability.

内容的提问来源于stack exchange，提问作者Richlewis