PgSql.map constructor

Looks for named parameters in sql and desugars them.

You can specify a character that starts parameters (by default, @ is used). In those queries, @variableName can be used to declare a variable.

final sql = PgSql.map('SELECT * FROM users WHERE id = @id');
final stmt = await connection.prepare(sql);
final vars = {'id': PgTypedParameter(PgDataType.integer, 3)};
await for (final row in stmt.bind(vars)) {
  // Got user with id 3
}

To make this more consise, you can also supply the type of the variable in the query:

final sql = PgSql.map('SELECT * FROM users WHERE id = @id:int4');
final stmt = await connection.prepare(sql);
final vars = {'id': 3};
await for (final row in stmt.bind(vars)) {
  // Got user with id 3
}

String literals, identifiers and comments are correctly ignored. So for instance, the following query only uses one variable (id):

SELECT name AS "@handle" FROM users WHERE id = @id; -- select @users

Note that this syntax is a feature of this package and not directly understood by postgres. This requires the package to scan the sql for variables, which adds a small overhead over when compared to a direct PgSql query. Also, the scanner might interpret queries incorrectly in the case of malformed sql (like an unterminated string literal or comment). In that case, the transformation might not recognize all variables.

Implementation

PgSql class