0
# SQL Functions
1

2
This document covers built-in SQL functions for aggregation, string manipulation, date operations, mathematical calculations, and database-specific functions available through the SQL builder.
3

4
## Functions Module
5

6
The functions module provides access to standard SQL functions and allows creation of custom function calls.
7

8
```javascript { .api }
9
// Access standard functions
10
sql.functions: StandardFunctions;
11

12
// Create custom function calls
13
sql.function(functionName: string): (...args: any[]) => FunctionCall;
14
sql.functionCallCreator(functionName: string): (...args: any[]) => FunctionCall;
15

16
// Function call interface
17
interface FunctionCall {
18
  toQuery(): QueryResult;
19
  toNode(): FunctionCallNode;
20
  as(alias: string): ColumnNode;
21
}
22

23
// Get functions programmatically
24
function getFunctions(functionNames: string | string[]): object | function;
25
function getStandardFunctions(): StandardFunctions;
26
```
27

28
## Standard Functions Interface
29

30
```javascript { .api }
31
interface StandardFunctions {
32
  // Aggregate functions
33
  AVG(column: Column): FunctionCall;
34
  COUNT(column?: Column): FunctionCall;
35
  DISTINCT(column: Column): FunctionCall;
36
  MAX(column: Column): FunctionCall;
37
  MIN(column: Column): FunctionCall;
38
  SUM(column: Column): FunctionCall;
39
  
40
  // String functions
41
  ABS(value: any): FunctionCall;
42
  COALESCE(...values: any[]): FunctionCall;
43
  LEFT(string: any, length: number): FunctionCall;
44
  LENGTH(string: any): FunctionCall;
45
  LOWER(string: any): FunctionCall;
46
  LTRIM(string: any): FunctionCall;
47
  RANDOM(): FunctionCall;
48
  RIGHT(string: any, length: number): FunctionCall;
49
  ROUND(number: any, precision?: number): FunctionCall;
50
  RTRIM(string: any): FunctionCall;
51
  SUBSTR(string: any, start: number, length?: number): FunctionCall;
52
  TRIM(string: any): FunctionCall;
53
  UPPER(string: any): FunctionCall;
54
  
55
  // Date functions
56
  YEAR(date: any): FunctionCall;
57
  MONTH(date: any): FunctionCall;
58
  DAY(date: any): FunctionCall;
59
  HOUR(date: any): FunctionCall;
60
  CURRENT_TIMESTAMP(): FunctionCall;
61
  
62
  // PostgreSQL-specific functions
63
  HSTORE(...pairs: any[]): FunctionCall;
64
  TS_RANK(vector: any, query: any): FunctionCall;
65
  TS_RANK_CD(vector: any, query: any): FunctionCall;
66
  PLAINTO_TSQUERY(config: any, text: any): FunctionCall;
67
  TO_TSQUERY(config: any, text: any): FunctionCall;
68
  TO_TSVECTOR(config: any, document: any): FunctionCall;
69
  SETWEIGHT(vector: any, weight: string): FunctionCall;
70
}
71
```
72

73
## Aggregate Functions
74

75
### COUNT
76

77
```javascript { .api }
78
COUNT(): FunctionCall;              // COUNT(*)
79
COUNT(column: Column): FunctionCall; // COUNT(column)
80
```
81

82
Usage examples:
83

84
```javascript
85
// Count all rows
86
const countAllQuery = user
87
  .select(sql.functions.COUNT().as('total_users'))
88
  .toQuery();
89

90
// Count specific column
91
const countEmailQuery = user
92
  .select(sql.functions.COUNT(user.email).as('users_with_email'))
93
  .toQuery();
94

95
// Count with GROUP BY
96
const countByDeptQuery = user
97
  .select(
98
    user.department,
99
    sql.functions.COUNT(user.id).as('user_count')
100
  )
101
  .group(user.department)
102
  .toQuery();
103

104
// Count distinct
105
const distinctCountQuery = user
106
  .select(sql.functions.COUNT(sql.functions.DISTINCT(user.department)).as('dept_count'))
107
  .toQuery();
108
```
109

110
### SUM, AVG, MIN, MAX
111

112
```javascript { .api }
113
SUM(column: Column): FunctionCall;
114
AVG(column: Column): FunctionCall;
115
MIN(column: Column): FunctionCall;
116
MAX(column: Column): FunctionCall;
117
```
118

119
Usage examples:
120

121
```javascript
122
// Sales statistics
123
const salesStatsQuery = order
124
  .select(
125
    sql.functions.SUM(order.amount).as('total_sales'),
126
    sql.functions.AVG(order.amount).as('avg_order_value'),
127
    sql.functions.MIN(order.amount).as('min_order'),
128
    sql.functions.MAX(order.amount).as('max_order'),
129
    sql.functions.COUNT(order.id).as('order_count')
130
  )
131
  .toQuery();
132

133
// Monthly aggregates
134
const monthlyStatsQuery = order
135
  .select(
136
    sql.functions.MONTH(order.created_at).as('month'),
137
    sql.functions.SUM(order.amount).as('monthly_total'),
138
    sql.functions.AVG(order.amount).as('monthly_avg')
139
  )
140
  .group(sql.functions.MONTH(order.created_at))
141
  .toQuery();
142
```
143

144
## String Functions
145

146
### Case Conversion
147

148
```javascript { .api }
149
UPPER(string: any): FunctionCall;
150
LOWER(string: any): FunctionCall;
151
```
152

153
Usage examples:
154

155
```javascript
156
// Case conversion
157
const caseQuery = user
158
  .select(
159
    user.id,
160
    sql.functions.UPPER(user.name).as('name_upper'),
161
    sql.functions.LOWER(user.email).as('email_lower')
162
  )
163
  .toQuery();
164

165
// Case-insensitive search with UPPER
166
const searchQuery = user
167
  .select()
168
  .where(sql.functions.UPPER(user.name).like(sql.functions.UPPER('%john%')))
169
  .toQuery();
170
```
171

172
### String Manipulation
173

174
```javascript { .api }
175
LENGTH(string: any): FunctionCall;
176
SUBSTR(string: any, start: number, length?: number): FunctionCall;
177
LEFT(string: any, length: number): FunctionCall;
178
RIGHT(string: any, length: number): FunctionCall;
179
TRIM(string: any): FunctionCall;
180
LTRIM(string: any): FunctionCall;
181
RTRIM(string: any): FunctionCall;
182
```
183

184
Usage examples:
185

186
```javascript
187
// String length and substring
188
const stringOpsQuery = user
189
  .select(
190
    user.name,
191
    sql.functions.LENGTH(user.name).as('name_length'),
192
    sql.functions.SUBSTR(user.name, 1, 10).as('name_short'),
193
    sql.functions.LEFT(user.email, 5).as('email_prefix'),
194
    sql.functions.RIGHT(user.phone, 4).as('phone_last_four')
195
  )
196
  .toQuery();
197

198
// String trimming
199
const trimQuery = user
200
  .select(
201
    user.id,
202
    sql.functions.TRIM(user.name).as('name_trimmed'),
203
    sql.functions.LTRIM(user.description).as('desc_left_trimmed')
204
  )
205
  .toQuery();
206

207
// Filter by string length
208
const lengthFilterQuery = user
209
  .select()
210
  .where(sql.functions.LENGTH(user.password).gte(8))
211
  .toQuery();
212
```
213

214
### COALESCE
215

216
```javascript { .api }
217
COALESCE(...values: any[]): FunctionCall;
218
```
219

220
Usage examples:
221

222
```javascript
223
// Handle null values
224
const coalesceQuery = user
225
  .select(
226
    user.id,
227
    sql.functions.COALESCE(user.nick_name, user.first_name, 'Anonymous').as('display_name'),
228
    sql.functions.COALESCE(user.phone, user.email, 'No contact').as('contact_info')
229
  )
230
  .toQuery();
231
```
232

233
## Mathematical Functions
234

235
### ABS and ROUND
236

237
```javascript { .api }
238
ABS(value: any): FunctionCall;
239
ROUND(number: any, precision?: number): FunctionCall;
240
RANDOM(): FunctionCall;
241
```
242

243
Usage examples:
244

245
```javascript
246
// Mathematical operations
247
const mathQuery = transaction
248
  .select(
249
    transaction.id,
250
    transaction.amount,
251
    sql.functions.ABS(transaction.amount).as('abs_amount'),
252
    sql.functions.ROUND(transaction.amount, 2).as('rounded_amount')
253
  )
254
  .toQuery();
255

256
// Random sampling
257
const randomSampleQuery = user
258
  .select()
259
  .order(sql.functions.RANDOM())
260
  .limit(10)
261
  .toQuery();
262

263
// Financial calculations
264
const financialQuery = account
265
  .select(
266
    account.id,
267
    sql.functions.ROUND(account.balance * 1.05, 2).as('balance_with_interest'),
268
    sql.functions.ABS(account.balance - account.credit_limit).as('available_credit')
269
  )
270
  .toQuery();
271
```
272

273
## Date Functions
274

275
### Date Extraction
276

277
```javascript { .api }
278
YEAR(date: any): FunctionCall;
279
MONTH(date: any): FunctionCall;
280
DAY(date: any): FunctionCall;
281
HOUR(date: any): FunctionCall;
282
CURRENT_TIMESTAMP(): FunctionCall;
283
```
284

285
Usage examples:
286

287
```javascript
288
// Date extraction
289
const dateQuery = order
290
  .select(
291
    order.id,
292
    order.created_at,
293
    sql.functions.YEAR(order.created_at).as('order_year'),
294
    sql.functions.MONTH(order.created_at).as('order_month'),
295
    sql.functions.DAY(order.created_at).as('order_day'),
296
    sql.functions.HOUR(order.created_at).as('order_hour')
297
  )
298
  .toQuery();
299

300
// Current timestamp
301
const timestampQuery = user
302
  .update({ last_login: sql.functions.CURRENT_TIMESTAMP() })
303
  .where(user.id.equals(1))
304
  .toQuery();
305

306
// Date grouping and aggregation
307
const monthlySalesQuery = order
308
  .select(
309
    sql.functions.YEAR(order.created_at).as('year'),
310
    sql.functions.MONTH(order.created_at).as('month'),
311
    sql.functions.SUM(order.amount).as('monthly_total')
312
  )
313
  .group(
314
    sql.functions.YEAR(order.created_at),
315
    sql.functions.MONTH(order.created_at)
316
  )
317
  .order(
318
    sql.functions.YEAR(order.created_at).desc,
319
    sql.functions.MONTH(order.created_at).desc
320
  )
321
  .toQuery();
322
```
323

324
## PostgreSQL-Specific Functions
325

326
### HSTORE Functions
327

328
```javascript { .api }
329
HSTORE(...pairs: any[]): FunctionCall;
330
```
331

332
Usage examples:
333

334
```javascript
335
// Create HSTORE from key-value pairs
336
const hstoreQuery = user
337
  .update({
338
    metadata: sql.functions.HSTORE('last_login', new Date(), 'ip_address', '192.168.1.1')
339
  })
340
  .where(user.id.equals(1))
341
  .toQuery();
342
```
343

344
### Text Search Functions
345

346
```javascript { .api }
347
TS_RANK(vector: any, query: any): FunctionCall;
348
TS_RANK_CD(vector: any, query: any): FunctionCall;
349
PLAINTO_TSQUERY(config: any, text: any): FunctionCall;
350
TO_TSQUERY(config: any, text: any): FunctionCall;
351
TO_TSVECTOR(config: any, document: any): FunctionCall;
352
SETWEIGHT(vector: any, weight: string): FunctionCall;
353
```
354

355
Usage examples:
356

357
```javascript
358
// Full-text search setup
359
const searchSetupQuery = document
360
  .update({
361
    search_vector: sql.functions.TO_TSVECTOR('english', document.title.concat(' ').concat(document.content))
362
  })
363
  .toQuery();
364

365
// Full-text search query
366
const searchQuery = document
367
  .select(
368
    document.id,
369
    document.title,
370
    sql.functions.TS_RANK(
371
      document.search_vector,
372
      sql.functions.PLAINTO_TSQUERY('english', 'search terms')
373
    ).as('rank')
374
  )
375
  .where(
376
    document.search_vector.match(
377
      sql.functions.PLAINTO_TSQUERY('english', 'search terms')
378
    )
379
  )
380
  .order(
381
    sql.functions.TS_RANK(
382
      document.search_vector,
383
      sql.functions.PLAINTO_TSQUERY('english', 'search terms')
384
    ).desc
385
  )
386
  .toQuery();
387

388
// Advanced text search with weights
389
const weightedSearchQuery = document
390
  .select(
391
    document.id,
392
    document.title,
393
    sql.functions.TS_RANK_CD(
394
      sql.functions.SETWEIGHT(sql.functions.TO_TSVECTOR('english', document.title), 'A')
395
        .concat(sql.functions.SETWEIGHT(sql.functions.TO_TSVECTOR('english', document.content), 'B')),
396
      sql.functions.TO_TSQUERY('english', 'search & terms')
397
    ).as('rank')
398
  )
399
  .toQuery();
400
```
401

402
## Custom Functions
403

404
### Creating Custom Function Calls
405

406
```javascript { .api }
407
// Create single function
408
sql.function(functionName: string): (...args: any[]) => FunctionCall;
409

410
// Create function call creator
411
sql.functionCallCreator(functionName: string): (...args: any[]) => FunctionCall;
412

413
// Create multiple functions
414
getFunctions(functionNames: string[]): { [name: string]: (...args: any[]) => FunctionCall };
415
```
416

417
Usage examples:
418

419
```javascript
420
// Custom function call
421
const customFunction = sql.function('CUSTOM_FUNC');
422
const customQuery = user
423
  .select(
424
    user.id,
425
    customFunction(user.data, 'parameter').as('custom_result')
426
  )
427
  .toQuery();
428

429
// Database-specific functions
430
const postgresArrayAgg = sql.function('ARRAY_AGG');
431
const arrayAggQuery = user
432
  .select(
433
    user.department,
434
    postgresArrayAgg(user.name).as('user_names')
435
  )
436
  .group(user.department)
437
  .toQuery();
438

439
// JSON functions (PostgreSQL)
440
const jsonFunctions = sql.function('JSON_BUILD_OBJECT');
441
const jsonQuery = user
442
  .select(
443
    user.id,
444
    jsonFunctions('name', user.name, 'email', user.email, 'age', user.age).as('user_json')
445
  )
446
  .toQuery();
447

448
// Window functions
449
const windowFunction = sql.function('ROW_NUMBER');
450
const windowQuery = user
451
  .select(
452
    user.id,
453
    user.name,
454
    user.salary,
455
    windowFunction().over().partitionBy(user.department).orderBy(user.salary.desc).as('salary_rank')
456
  )
457
  .toQuery();
458
```
459

460
### Function Composition
461

462
```javascript
463
// Combine multiple functions
464
const complexQuery = order
465
  .select(
466
    order.id,
467
    sql.functions.ROUND(
468
      sql.functions.AVG(order.amount), 
469
      2
470
    ).as('avg_amount_rounded'),
471
    sql.functions.UPPER(
472
      sql.functions.SUBSTR(order.status, 1, 3)
473
    ).as('status_code')
474
  )
475
  .group(order.customer_id)
476
  .toQuery();
477

478
// Nested function calls
479
const nestedQuery = user
480
  .select(
481
    user.id,
482
    sql.functions.LENGTH(
483
      sql.functions.TRIM(
484
        sql.functions.UPPER(user.name)
485
      )
486
    ).as('clean_name_length')
487
  )
488
  .toQuery();
489
```
490

491
## Dialect-Specific Function Support
492

493
### MySQL-Specific Functions
494

495
```javascript
496
// MySQL functions
497
const mysqlFunctions = {
498
  CONCAT: sql.function('CONCAT'),
499
  DATE_FORMAT: sql.function('DATE_FORMAT'),
500
  SUBSTRING_INDEX: sql.function('SUBSTRING_INDEX'),
501
  GROUP_CONCAT: sql.function('GROUP_CONCAT')
502
};
503

504
const mysqlQuery = user
505
  .select(
506
    user.id,
507
    mysqlFunctions.CONCAT(user.first_name, ' ', user.last_name).as('full_name'),
508
    mysqlFunctions.DATE_FORMAT(user.created_at, '%Y-%m-%d').as('created_date')
509
  )
510
  .toQuery();
511
```
512

513
### SQL Server-Specific Functions
514

515
```javascript
516
// SQL Server functions
517
const sqlServerFunctions = {
518
  ISNULL: sql.function('ISNULL'),
519
  DATEPART: sql.function('DATEPART'),
520
  CHARINDEX: sql.function('CHARINDEX')
521
};
522

523
const sqlServerQuery = user
524
  .select(
525
    user.id,
526
    sqlServerFunctions.ISNULL(user.middle_name, '').as('middle_name'),
527
    sqlServerFunctions.DATEPART('year', user.created_at).as('created_year')
528
  )
529
  .toQuery();
530
```
531

532
### Oracle-Specific Functions
533

534
```javascript
535
// Oracle functions
536
const oracleFunctions = {
537
  NVL: sql.function('NVL'),
538
  DECODE: sql.function('DECODE'),
539
  EXTRACT: sql.function('EXTRACT')
540
};
541

542
const oracleQuery = user
543
  .select(
544
    user.id,
545
    oracleFunctions.NVL(user.nick_name, user.first_name).as('display_name'),
546
    oracleFunctions.EXTRACT('YEAR FROM', user.created_at).as('created_year')
547
  )
548
  .toQuery();
549
```
550

551
## Function Usage Patterns
552

553
### Conditional Functions
554

555
```javascript
556
// Use functions in WHERE clauses
557
const functionalFilterQuery = user
558
  .select()
559
  .where(sql.functions.LENGTH(user.password).gte(8))
560
  .where(sql.functions.UPPER(user.status).equals('ACTIVE'))
561
  .toQuery();
562

563
// Use functions in HAVING clauses
564
const havingFunctionQuery = order
565
  .select(
566
    order.customer_id,
567
    sql.functions.COUNT(order.id).as('order_count'),
568
    sql.functions.AVG(order.amount).as('avg_amount')
569
  )
570
  .group(order.customer_id)
571
  .having(sql.functions.COUNT(order.id).gt(5))
572
  .having(sql.functions.AVG(order.amount).gt(100))
573
  .toQuery();
574
```
575

576
### Functions in ORDER BY
577

578
```javascript
579
// Order by function results
580
const functionOrderQuery = user
581
  .select()
582
  .order(
583
    sql.functions.LENGTH(user.name).desc,
584
    sql.functions.UPPER(user.last_name).asc
585
  )
586
  .toQuery();
587
```
588

589
### Functions with Aggregation
590

591
```javascript
592
// Complex aggregation with functions
593
const complexAggQuery = sales
594
  .select(
595
    sql.functions.YEAR(sales.date).as('year'),
596
    sql.functions.MONTH(sales.date).as('month'),
597
    sql.functions.COUNT(sales.id).as('transaction_count'),
598
    sql.functions.SUM(sales.amount).as('total_sales'),
599
    sql.functions.ROUND(sql.functions.AVG(sales.amount), 2).as('avg_sale'),
600
    sql.functions.MIN(sales.amount).as('min_sale'),
601
    sql.functions.MAX(sales.amount).as('max_sale')
602
  )
603
  .group(
604
    sql.functions.YEAR(sales.date),
605
    sql.functions.MONTH(sales.date)
606
  )
607
  .order(
608
    sql.functions.YEAR(sales.date).desc,
609
    sql.functions.MONTH(sales.date).desc
610
  )
611
  .toQuery();
612
```