Skip to content

Commit 8075ef9

Browse files
taozhi8833998taozhi8833998
committed
feat: support crosstab tablefunc in pg
1 parent 66ddb93 commit 8075ef9

4 files changed

Lines changed: 55 additions & 25 deletions

File tree

‎pegjs/postgresql.pegjs‎

Lines changed: 15 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -4311,8 +4311,22 @@ trim_func_clause
43114311
};
43124312
}
43134313

4314+
tablefunc_clause
4315+
= 'crosstab'i __ LPAREN __ s:literal_list __ RPAREN __ KW_AS __ 'final_result' LPAREN __ cds:column_data_type_list __ RPAREN {
4316+
return {
4317+
type: 'tablefunc',
4318+
name: 'crosstab',
4319+
args: { type: 'expr_list', value: s },
4320+
as: {
4321+
type: 'function',
4322+
name: 'final_result',
4323+
args: { type: 'expr_list', value: cds.map(v => ({ ...v, type: 'column_definition' })) },
4324+
}
4325+
}
4326+
}
4327+
43144328
func_call
4315-
= trim_func_clause
4329+
= trim_func_clause / tablefunc_clause
43164330
/ name:'now'i __ LPAREN __ l:expr_list? __ RPAREN __ 'at'i __ KW_TIME __ 'zone'i __ z:literal_string {
43174331
// => { type: 'function'; name: string; args: expr_list; suffix: literal_string; }
43184332
z.prefix = 'at time zone'

‎src/expr.js‎

Lines changed: 26 additions & 24 deletions
Original file line numberDiff line numberDiff line change
@@ -4,8 +4,8 @@ import { aggrToSQL } from './aggregation'
44
import { assignToSQL } from './assign'
55
import { binaryToSQL } from './binary'
66
import { caseToSQL } from './case'
7-
import { columnRefToSQL, fullTextSearchToSQL } from './column'
8-
import { anyValueFuncToSQL, castToSQL, extractFunToSQL, flattenFunToSQL, funcToSQL } from './func'
7+
import { columnDefinitionToSQL, columnRefToSQL, fullTextSearchToSQL } from './column'
8+
import { anyValueFuncToSQL, castToSQL, extractFunToSQL, flattenFunToSQL, funcToSQL, tablefuncFunToSQL } from './func'
99
import { intervalToSQL } from './interval'
1010
import { selectToSQL } from './select'
1111
import { showToSQL } from './show'
@@ -15,28 +15,30 @@ import { unionToSQL } from './union'
1515
import { namedWindowExprListToSQL, windowFuncToSQL } from './window'
1616

1717
const exprToSQLConvertFn = {
18-
alter : alterExprToSQL,
19-
aggr_func : aggrToSQL,
20-
any_value : anyValueFuncToSQL,
21-
window_func : windowFuncToSQL,
22-
'array' : arrayStructExprToSQL,
23-
assign : assignToSQL,
24-
binary_expr : binaryToSQL,
25-
case : caseToSQL,
26-
cast : castToSQL,
27-
column_ref : columnRefToSQL,
28-
datatype : dataTypeToSQL,
29-
extract : extractFunToSQL,
30-
flatten : flattenFunToSQL,
31-
fulltext_search : fullTextSearchToSQL,
32-
function : funcToSQL,
33-
insert : unionToSQL,
34-
interval : intervalToSQL,
35-
show : showToSQL,
36-
struct : arrayStructExprToSQL,
37-
tables : tablesToSQL,
38-
unnest : unnestToSQL,
39-
'window' : namedWindowExprListToSQL,
18+
alter : alterExprToSQL,
19+
aggr_func : aggrToSQL,
20+
any_value : anyValueFuncToSQL,
21+
window_func : windowFuncToSQL,
22+
'array' : arrayStructExprToSQL,
23+
assign : assignToSQL,
24+
binary_expr : binaryToSQL,
25+
case : caseToSQL,
26+
cast : castToSQL,
27+
column_ref : columnRefToSQL,
28+
column_definition : columnDefinitionToSQL,
29+
datatype : dataTypeToSQL,
30+
extract : extractFunToSQL,
31+
flatten : flattenFunToSQL,
32+
fulltext_search : fullTextSearchToSQL,
33+
function : funcToSQL,
34+
insert : unionToSQL,
35+
interval : intervalToSQL,
36+
show : showToSQL,
37+
struct : arrayStructExprToSQL,
38+
tablefunc : tablefuncFunToSQL,
39+
tables : tablesToSQL,
40+
unnest : unnestToSQL,
41+
'window' : namedWindowExprListToSQL,
4042
}
4143

4244
function varToSQL(expr) {

‎src/func.js‎

Lines changed: 7 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -83,10 +83,17 @@ function funcToSQL(expr) {
8383
return [parentheses ? `(${str})` : str, collateStr, overStr].filter(hasVal).join(' ')
8484
}
8585

86+
function tablefuncFunToSQL(expr) {
87+
const { as, name, args } = expr
88+
const result = [`${name}(${exprToSQL(args).join(', ')})`, 'AS', funcToSQL(as)]
89+
return result.join(' ')
90+
}
91+
8692
export {
8793
anyValueFuncToSQL,
8894
castToSQL,
8995
extractFunToSQL,
9096
flattenFunToSQL,
9197
funcToSQL,
98+
tablefuncFunToSQL,
9299
}

‎test/postgres.spec.js‎

Lines changed: 7 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1579,6 +1579,13 @@ describe('Postgres', () => {
15791579
'CREATE TABLE "test" ("amount" DOUBLE PRECISION)'
15801580
]
15811581
},
1582+
{
1583+
title: 'crosstab tablefunc',
1584+
sql: [
1585+
`SELECT * FROM crosstab( 'select student, subject, evaluation_result from evaluations order by 1,2') AS final_result(Student TEXT, Geography NUMERIC,History NUMERIC,Language NUMERIC,Maths NUMERIC,Music NUMERIC);`,
1586+
`SELECT * FROM crosstab('select student, subject, evaluation_result from evaluations order by 1,2') AS final_result("Student" TEXT, "Geography" NUMERIC, "History" NUMERIC, "Language" NUMERIC, "Maths" NUMERIC, "Music" NUMERIC)`
1587+
]
1588+
},
15821589
]
15831590
neatlyNestTestedSQL(SQL_LIST)
15841591
})

0 commit comments

Comments
 (0)