Skip to content

Commit 2d05d4e

Browse files
taozhi8833998taozhi8833998
committed
feat: support at time zone in pg
1 parent d805ed0 commit 2d05d4e

5 files changed

Lines changed: 57 additions & 8 deletions

File tree

‎pegjs/postgresql.pegjs‎

Lines changed: 31 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -3292,13 +3292,39 @@ cast_data_type
32923292
return t
32933293
}
32943294

3295+
column_item_suffix
3296+
= 'AT' __ KW_TIME __ 'ZONE'i __ t:quoted_ident_type {
3297+
// => [{ type: 'origin'; value: string; }, quoted_ident_type]
3298+
return [
3299+
{
3300+
type: 'origin',
3301+
value: 'at time zone'
3302+
},
3303+
t
3304+
]
3305+
}
32953306
column_list_item
32963307
= c:string_constants_escape {
32973308
// => { expr: expr; as: null; }
32983309
return { expr: c, as: null }
32993310
}
3300-
/ e:(column_ref_quoted / expr_item) __ s:KW_DOUBLE_COLON __ t:cast_data_type __ tail:(__ (additive_operator / multiplicative_operator) __ expr_item)* __ alias:alias_clause? {
3311+
/ e:column_ref __ s:KW_DOUBLE_COLON __ t:cast_data_type __ cs:column_item_suffix+ __ alias:alias_clause? {
3312+
// => { type: 'cast'; expr: expr; symbol: '::'; target: cast_data_type; as?: null; }
3313+
t.suffix = cs.flat()
3314+
return {
3315+
as: alias,
3316+
type: 'cast',
3317+
expr: e,
3318+
symbol: '::',
3319+
target: t,
3320+
suffix: cs.flat(),
3321+
}
3322+
}
3323+
/ e:(column_ref_quoted / expr_item) __ s:KW_DOUBLE_COLON __ t:cast_data_type __ tail:(__ (additive_operator / multiplicative_operator) __ expr_item)* __ cs:column_item_suffix* __ alias:alias_clause? {
33013324
// => { type: 'cast'; expr: expr; symbol: '::'; target: cast_data_type; as?: null; }
3325+
if (e.type === 'column_ref' && cs.length) {
3326+
e.column.options = { type: 'expr_list', value: cs.flat(), separator: ' ' }
3327+
}
33023328
return {
33033329
as: alias,
33043330
type: 'cast',
@@ -3341,7 +3367,7 @@ column_list_item
33413367
as: null
33423368
};
33433369
}
3344-
/ e:expr_item __ alias:alias_clause? {
3370+
/ e:expr_item __ alias:alias_clause? {
33453371
// => { type: 'expr'; expr: expr; as?: alias_clause; }
33463372
return { type: 'expr', expr: e, as: alias };
33473373
}
@@ -4074,7 +4100,7 @@ value_item
40744100

40754101
expr_list
40764102
= head:expr tail:(__ COMMA __ expr)* {
4077-
// => { type: 'expr_list'; value: expr[] }
4103+
// => { type: 'expr_list'; value: expr[]; parentheses?: boolean; separator?: string; }
40784104
const el = { type: 'expr_list' };
40794105
el.value = createList(head, tail);
40804106
return el;
@@ -4404,9 +4430,10 @@ multiplicative_operator
44044430
= "*" / "/" / "%" / "||"
44054431

44064432
column_ref_array_index
4407-
= c:column_ref __ a:array_index_list? {
4433+
= c:column_ref __ a:array_index_list? __ cs:column_item_suffix* {
44084434
// => column_ref
44094435
if (a) c.array_index = a
4436+
if (cs.length) c.options = { type: 'expr_list', value: cs.flat(), separator: ' ' }
44104437
return c
44114438
}
44124439

‎src/column.js‎

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -35,7 +35,7 @@ function arrayIndexToSQL(arrayIndexList) {
3535
}
3636
function columnRefToSQL(expr) {
3737
const {
38-
array_index, as, column, db, isDual, notations = [], schema, table, parentheses,
38+
array_index, as, column, db, isDual, notations = [], options, schema, table, parentheses,
3939
suffix, order_by, subFields = [],
4040
} = expr
4141
let str = column === '*' ? '*' : columnOffsetToSQL(column, isDual)
@@ -51,6 +51,7 @@ function columnRefToSQL(expr) {
5151
str = [`${str}${arrayIndexToSQL(array_index)}`, ...subFields].join('.')
5252
const result = [
5353
str,
54+
exprToSQL(options),
5455
commonOptionConnector('AS', exprToSQL, as),
5556
]
5657
result.push(typeof suffix === 'string' ? toUpper(suffix) : exprToSQL(suffix))

‎src/expr.js‎

Lines changed: 6 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -89,8 +89,12 @@ function getExprListSQL(exprList) {
8989
}
9090

9191
exprToSQLConvertFn.expr_list = expr => {
92-
const str = getExprListSQL(expr.value)
93-
return expr.parentheses ? `(${str.join(', ')})` : str
92+
const result = getExprListSQL(expr.value)
93+
const { parentheses, separator } = expr
94+
if (!parentheses && !separator) return result
95+
const joinSymbol = separator || ', '
96+
const str = result.join(joinSymbol)
97+
return parentheses ? `(${str})` : str
9498
}
9599

96100
exprToSQLConvertFn.select = expr => {

‎test/postgres.spec.js‎

Lines changed: 15 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1657,7 +1657,7 @@ describe('Postgres', () => {
16571657
]
16581658
},
16591659
{
1660-
title: 'substring',
1660+
title: 'substring function',
16611661
sql: [
16621662
`SELECT AVG(
16631663
CASE
@@ -1672,6 +1672,20 @@ describe('Postgres', () => {
16721672
`SELECT AVG(CASE WHEN "duration" LIKE '%min%' THEN CAST(SUBSTRING("duration" FROM '([0-9]+)') AS INTEGER) WHEN "duration" LIKE '%hr%' THEN CAST(SUBSTRING("duration" FROM '([0-9]+)') AS INTEGER) * 60 ELSE NULL END) AS "average_duration" FROM "netflix_shows" WHERE "listed_in" ILIKE '%Thriller%' LIMIT 100`
16731673
]
16741674
},
1675+
{
1676+
title: 'column at time zone',
1677+
sql: [
1678+
"SELECT start_time AT TIME ZONE 'UTC' AS start_time FROM my_table",
1679+
`SELECT start_time AT TIME ZONE 'UTC' AS "start_time" FROM "my_table"`
1680+
]
1681+
},
1682+
{
1683+
title: 'column cast with at time zone',
1684+
sql: [
1685+
"SELECT start_time::timestamp AT TIME ZONE 'UTC' AS start_time FROM my_table",
1686+
`SELECT start_time::TIMESTAMP AT TIME ZONE 'UTC' AS "start_time" FROM "my_table"`
1687+
]
1688+
},
16751689
]
16761690
function neatlyNestTestedSQL(sqlList){
16771691
sqlList.forEach(sqlInfo => {

‎types.d.ts‎

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -103,6 +103,7 @@ export interface ColumnRefItem {
103103
type: "column_ref";
104104
table: string | null;
105105
column: string | { expr: ValueExpr };
106+
options?: ExprList;
106107
loc?: LocationRange;
107108
}
108109
export interface ColumnRefExpr {
@@ -220,6 +221,8 @@ export type ExprList = {
220221
type: "expr_list";
221222
value: ExpressionValue[];
222223
loc?: LocationRange;
224+
parentheses?: boolean;
225+
separator?: string;
223226
};
224227

225228
export type PartitionBy = {

0 commit comments

Comments
 (0)