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

[pm0u]

I am also having an issue with this, attempting to string together multiple subqueries in order to string aggregate to mvt tiles

export const queryLayer = ({
  z,
  x,
  y,
  layer,
  fields,
}: {
  z: string;
  x: string;
  y: string;
  layer: string;
  fields: string[];
}) => sql`
  (SELECT ST_AsMVT(q, '${layer}', 4096, 'geom') AS l FROM
        (SELECT ST_AsMvtGeom(
            geom,
            ST_TileEnvelope(${z}, ${x}, ${y}),
            4096,
            64,
            true
            ) AS geom, ${fields.join(", ")}
       FROM ${layer} WHERE (geom && ST_TileEnvelope(${z}, ${x}, ${y}))) AS q)
  `;

export const tileQuery = ({
  z,
  x,
  y,
  layers = allLayers,
}: {
  z: string;
  x: string;
  y: string;
  layers?: Array<keyof typeof layerDefs>;
}) => {
  return sql`select (${layers
    .map((layer) => queryLayer({ z, x, y, layer, fields: layerDefs[layer] }))
    .join(" || ")
    .replace(/\s+/g, " ")}) as geom`;
};

Resulting in a (functional) query such as:

  select ((SELECT ST_AsMVT(q, 'table', 4096, 'geom') AS l FROM
        (SELECT ST_AsMvtGeom(
            geom,
            ST_TileEnvelope(10, 209, 390),
            4096,
            64,
            true
            ) AS geom
         FROM table WHERE (geom && ST_TileEnvelope(10, 209, 390))) AS q)
         ||
      (SELECT ST_AsMVT(q, 'table2', 4096, 'geom') AS l FROM
        (SELECT ST_AsMvtGeom(
            geom,
            ST_TileEnvelope(10, 209, 390),
            4096,
            64,
            true
            ) AS geom
      FROM table2 WHERE (geom && ST_TileEnvelope(10, 209, 390))) AS q)
  ) as geom

But I end up with a string of unawaited promises

const tile = await tileQuery({z, x, y})

console.log({tile})

// output
{
  tile: Result(1) [ { geom: '[object Promise] || [object Promise]' } ]
}

The return type of queryLayer is postgres.PendingQuery<>

If I am reading the result correctly, it seems to be reading the subquery joined as a string to be selected as in

select ("arbitrary string") as mvt

it does work if I change queryTile to just return a string and wrap the map/join in sql.unsafe() however I would prefer to not interpolate raw values if possible