query method

@override Future<DbQueryResult> query(String sql, Map<String, dynamic> substitutionValues, { List<String> returningFields = const [], String? tableName, Map<String, QueryHint> hints = const {}, })

Run query.

Implementation

@override
Future<DbQueryResult> query(
    String sql, Map<String, dynamic> substitutionValues,
    {List<String> returningFields = const [],
    String? tableName,
    Map<String, QueryHint> hints = const {}}) async {
  if (returningFields.isNotEmpty) {
    var fields = returningFields.join(', ');
    var returning = 'RETURNING $fields';
    sql = '$sql $returning';
  }

  logger.config('query: $sql ; params: $substitutionValues');

  // expand List first
  var param = <String, dynamic>{};
  substitutionValues.forEach((key, value) {
    if (QueryHint.lob == hints[key]) {
      sql = sql.replaceAll('@$key', '@$key:bytea ');
      param[key] = value;
    } else if (value is List) {
      var newKeys = [];
      for (var i = 0; i < value.length; i++) {
        var key2 = '${key}_$i';
        param[key2] = value[i];
        newKeys.add('@$key2');
      }

      var strReplace = "(${newKeys.join(',')})";
      sql = sql.replaceAll('@$key ',
          strReplace); // '@$key ' means all key must be followed by a ' ' to prevent mis-replace!
    } else {
      param[key] = value;
    }
  });

  logger.config('final query: $sql ; params: $param');

  return PgQueryResult(await _pool.run<PostgreSQLResult>((pgContext) async {
    return await pgContext.query(sql, substitutionValues: param);
  }));
}