how to run a subquery with a variable or call function to generate

[Md-Anamul-Haque]

const subq = "(SELECT CASE WHEN LENGTH((SELECT COALESCE(MAX(substr(title,1)), '0') FROM todo)) >= 5 THEN LENGTH((SELECT COALESCE(MAX(substr(title,1)), '0') FROM todo)) ELSE 5 END FROM todo)"
   const result = await sql`
   	${subq}
   	`;

		node:internal/process/promises:289
            triggerUncaughtException(err, true /* fromPromise */);
            ^

PostgresError: syntax error at or near "$1"
    at ErrorResponse (c:\Users\anamul\Desktop\test-dirzzle\node_modules\postgres\cjs\src\connection.js:788:26)
    at handle (c:\Users\anamul\Desktop\test-dirzzle\node_modules\postgres\cjs\src\connection.js:473:7)
    at Socket.data (c:\Users\anamul\Desktop\test-dirzzle\node_modules\postgres\cjs\src\connection.js:315:9)
    at Socket.emit (node:events:514:28)
    at Socket.emit (node:domain:488:12)
    at addChunk (node:internal/streams/readable:545:12)
    at readableAddChunkPushByteMode (node:internal/streams/readable:495:3)
    at Readable.push (node:internal/streams/readable:375:5)
    at TCP.onStreamRead (node:internal/stream_base_commons:190:23)
    at cachedError (c:\Users\anamul\Desktop\test-dirzzle\node_modules\postgres\cjs\src\query.js:170:23)
    at Query (c:\Users\anamul\Desktop\test-dirzzle\node_modules\postgres\cjs\src\query.js:36:24)
    at sql2 (c:\Users\anamul\Desktop\test-dirzzle\node_modules\postgres\cjs\src\index.js:112:11)
    at main (c:\Users\anamul\Desktop\test-dirzzle\src\index.ts:53:23) {
  severity_local: 'ERROR',
  severity: 'ERROR',
  code: '42601',
  position: '4',
  file: 'scan.l',
  line: '1245',
  routine: 'scanner_yyerror'
}

		```

[csabaxyz]

You can turn your sub-query into a query like this:

const subq = sql`(SELECT CASE WHEN LENGTH((SELECT COALESCE(MAX(substr(title,1)), '0') FROM todo)) >= 5 THEN LENGTH((SELECT COALESCE(MAX(substr(title,1)), '0') FROM todo)) ELSE 5 END FROM todo)`
const result = await sql`
  ${subq}
`;

Essentially you can nest different queries as long as there is no await before the sub-queries. Also there is sql.unsafe if you know what you are doing.

[Md-Anamul-Haque]

Will it call between the database 2 times at once?

[bas080]

@Md-Anamul-Haque , I suggest you enable debug (logging) so you can see what SQL queries are performed