pg-slang
Convert informal SQL SELECT to formal SQL.
NOTE: pg-english can convert english query to informal SQL.
-- ex: SLANG SELECT "food name", "calcium" FROM "apples"-- ex: SQL SELECT "name", "ca", "ca_e" FROM "compositions"WHERE TRUE AND (FALSE OR ("tsvector" @@ 'apples'))
const slang = ;// slang(<informal sql>, <map fn>, [this], [options])// -> Promise (formal sql) // <informal sql>:// SELECT "food name", "trans fat" FROM "food" ORDER BY "trans fat" DESC// SELECT "protein", "vitamin d" FROM "poultry ORDER BY "vitamin d" DESC// SELECT "sum: essential amino acids" AS "amino" FROM "meat" ORDER BY "amino"// ... // <map fn>(<text>, <type>, [hint], [from]):// - text: field name, like "food name", "trans fat", "food", ...// - type: field type, can be "from","columns", "where", "having", "orderBy", or "groupBy"// - hint: field hint, can be null, "all", "sum", or "avg"// - from: field from, will be null for type=table// -> Promise [<value>]// - value: expression string // [options]:// - from: default table// - limit: default maximum limit// - limits: table specific maximum limts
// 1. { return 'sample1' 'sample1';};;// SELECT "sample", "sample" FROM "sample" WHERE TRUE AND TRUE { iftype==='column' return 'name'; return 'compositions';};;// SELECT "name", "name" FROM "compositions" WHERE TRUE AND TRUE { iftype==='column' return 'ca' 'ca_e'; return 'compositions';};;// SELECT "ca", "ca_e", "ca", "ca_e" FROM "compositions" WHERE TRUE AND TRUE var columns = 'food code': 'code' 'food name': 'name' 'calcium': 'ca' 'ca_e' 'magnesium': 'mg' 'mg_e';var tables = 'food' 'compositions'; { iftype==='column' return columnstext; return tables? 'compositions':;};;// SELECT "name", "ca", "ca_e" FROM "null" WHERE TRUE AND TRUE { iftype==='column' return columnstext; return tables? 'compositions':`"tsvector" @@ ''`;};;// SELECT "name", "ca", "ca_e" FROM "null" WHERE TRUE AND (FALSE OR ("tsvector" @@ 'apples')) var options = from: 'compositions';;// SELECT "name", "ca", "ca_e" FROM "compositions" WHERE TRUE AND (FALSE OR ("tsvector" @@ 'apples')) // PRIMARY USECASE// --------------- { return { // ... // <do some database lookup> // ... };};;// SELECT "name", "ca", "ca_e" FROM "compositions" WHERE TRUE AND (FALSE OR ("tsvector" @@ 'apples')) // NOTES// -----// 1. Map function return value can be an expression array// 2. For column, return multiple values to select multiple columns// 3. But in expressions, only first return value is considered// 4. Hints perform an operation on matching columns// 5. Use hint to decide which columns to return// 6. For table, returning expression will append to where// 7. Return expression and table name for full association// 8. Hint can be used in column text as "<hint>: <column text>"// 9. Hint "all": all|each|every// 10. Hint "sum": sum|gross|total|whole|aggregate// 11. Hint "avg": avg|mid|par|mean|norm|center|centre|average|midpoint// 12. Experiment! And mail me for issues / pull requests!