Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

postgres-tvf #205

Merged
merged 1 commit into from
Sep 3, 2023
Merged

postgres-tvf #205

Show file tree
Hide file tree
Changes from all commits
Commits
Show all changes
1 commit
Select commit
File filter

Filter by extension

Filter by extension
Conversations
Failed to load comments.
Loading
Jump to
Jump to file
Failed to load files.
Loading
Diff view
Diff view
1 change: 1 addition & 0 deletions .vscode/launch.json
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -102,6 +102,7 @@
"select *, JSON_EXTRACT(sourceRanges, '$[0]') sr from google.compute.firewalls, json_each(sourceRanges) where project = 'stackql-demo';",
"select fw.id, JSON_EXTRACT(fw.sourceRanges, '$[0]') sr from google.compute.firewalls fw, json_each(sourceRanges) where project = 'stackql-demo';",
"select fw.id, JSON_EXTRACT(fw.sourceRanges, '$[0]') sr, json_each.value as source_range from google.compute.firewalls fw, json_each(sourceRanges) where project = 'stackql-demo';",
"select fw.id, rd.value as source_range from google.compute.firewalls fw, json_array_elements_text(sourceRanges) as rd where project = 'stackql-demo';",
],
"default": "show providers;"
},
Expand Down
2 changes: 1 addition & 1 deletion go.mod
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -23,7 +23,7 @@ require (
github.com/stackql/go-sqlite3 v0.0.2-stackqlbeta04
github.com/stackql/go-suffix-map v0.0.1-alpha01
github.com/stackql/psql-wire v0.1.1-alpha04
github.com/stackql/stackql-parser v0.0.13-beta18
github.com/stackql/stackql-parser v0.0.13-beta19
github.com/xo/dburl v0.12.4
golang.org/x/oauth2 v0.0.0-20211104180415-d3ed0bb246c8
golang.org/x/sync v0.0.0-20220722155255-886fb9371eb4
Expand Down
4 changes: 2 additions & 2 deletions go.sum
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -479,8 +479,8 @@ github.com/stackql/psql-wire v0.1.1-alpha04 h1:dUrForykNZEvB7u6tIlQ/xGYElRtfMPRE
github.com/stackql/psql-wire v0.1.1-alpha04/go.mod h1:a44Wd8kDC3irFLpGutarKDBqhJ/aqXlj1aMzO5bVJYg=
github.com/stackql/readline v0.0.2-alpha05 h1:ID4QzGdplFBsrSnTuz8pvKzWw96JbrJg8fsLry2UriU=
github.com/stackql/readline v0.0.2-alpha05/go.mod h1:OFAYOdXk/X4+5GYiDXFfaGrk+bCN6Qv0SYY5HNzD2E0=
github.com/stackql/stackql-parser v0.0.13-beta18 h1:p4cEMYpBX2uHw4CAofnae3pFcB3O4UQ8vDAIF752mHo=
github.com/stackql/stackql-parser v0.0.13-beta18/go.mod h1:iyB47SvRS+Fvpn7joF7mHAkeiWSq83TbUhglRmLzPLQ=
github.com/stackql/stackql-parser v0.0.13-beta19 h1:n3qvDqSK9bbqVHmv+tI9hxdPMYs8LbRgRLQVZ0wylxw=
github.com/stackql/stackql-parser v0.0.13-beta19/go.mod h1:iyB47SvRS+Fvpn7joF7mHAkeiWSq83TbUhglRmLzPLQ=
github.com/stackql/stackql-provider-registry v0.0.1-rc01 h1:3XorfaYEhGpuvEeQK04EmpZUCr42hZ/a4Nh3wE25LF0=
github.com/stackql/stackql-provider-registry v0.0.1-rc01/go.mod h1:D0WFWa7HOXS+PQAmvN0Nm1E6dMdUkUFtAzRH8cXxg88=
github.com/stretchr/objx v0.1.0/go.mod h1:HFkY916IF+rwdDfMAkV7OtwuqBVzrE8GR6GFx+wExME=
Expand Down
28 changes: 26 additions & 2 deletions internal/stackql/astformat/ast_format_postgres.go
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -19,6 +19,7 @@ var (
}
)

//nolint:gocognit // tactical
func PostgresSelectExprsFormatter(buf *sqlparser.TrackedBuffer, node sqlparser.SQLNode) {
switch node := node.(type) {
case *sqlparser.ColName:
Expand All @@ -29,15 +30,38 @@ func PostgresSelectExprsFormatter(buf *sqlparser.TrackedBuffer, node sqlparser.S
case *sqlparser.FuncExpr:
if strings.ToLower(node.Name.GetRawVal()) == constants.SQLFuncJSONExtractPostgres && len(node.Exprs) > 1 {
sb := sqlparser.NewTrackedBuffer(PostgresSelectExprsFormatter)
lhsSuffix := "::json"
sb.AstPrintf(node, "%s(%v%s, %v", constants.SQLFuncJSONExtractPostgres, node.Exprs[0], lhsSuffix, node.Exprs[1])
sb.AstPrintf(
node,
"%s(%v%s, %v",
constants.SQLFuncJSONExtractPostgres,
node.Exprs[0],
constants.PostgresJSONCastSuffix,
node.Exprs[1])
for _, val := range node.Exprs[2:] {
sb.AstPrintf(node, ", %v", val)
}
sb.AstPrintf(node, ")")
buf.WriteString(sb.String())
return
}
if strings.ToLower(node.Name.GetRawVal()) == sqlparser.JsonArrayElementsTextStr && len(node.Exprs) >= 1 {
sb := sqlparser.NewTrackedBuffer(PostgresSelectExprsFormatter)
sb.AstPrintf(
node,
"%s(%v%s",
sqlparser.JsonArrayElementsTextStr,
node.Exprs[0],
constants.PostgresJSONCastSuffix,
)
if len(node.Exprs) > 1 {
for _, val := range node.Exprs[1:] {
sb.AstPrintf(node, ", %v", val)
}
}
sb.AstPrintf(node, ")")
buf.WriteString(sb.String())
return
}
node.Format(buf)
return
case *sqlparser.GroupConcatExpr:
Expand Down
1 change: 1 addition & 0 deletions internal/stackql/constants/constants.go
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -19,6 +19,7 @@ const (
CSVStr string = "csv"
TextStr string = "text"
PostgresIDMaxWidth int = 63
PostgresJSONCastSuffix string = "::json"
PrettyTextStr string = "pptext"
DBEngineSQLite3Embedded string = "sqlite3_embedded"
DBEnginePostgresTCP string = "postgres_tcp"
Expand Down
11 changes: 8 additions & 3 deletions test/robot/functional/stackql_mocked_from_cmd_line.robot
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -1385,13 +1385,18 @@ Select Expression Function Expression Alias Reference Alongside Projection Retur
... ${outputStr}
... stdout=${CURDIR}/tmp/Select-Expression-Function-Expression-Alias-Reference-Alongside-Projection-Returns-Results.tmp

SQLite Table Valued Function Plus Projection Returns Expected Results
Pass Execution If "${SQL_BACKEND}" == "postgres_tcp" This is for sqlite table valued finctions only. This is a genuine case of difference.
${inputStr} = Catenate
Table Valued Function Plus Projection Returns Expected Results
${sqliteInputStr} = Catenate
... select fw.id, fw.name, json_each.value as source_range, json_each.value = '0.0.0.0/0' as is_entire_network
... from google.compute.firewalls fw, json_each(sourceRanges)
... where project = 'testing-project'
... order by name desc, source_range desc;
${postgresInputStr} = Catenate
... select fw.id, fw.name, rd.value as source_range, case when rd.value = '0.0.0.0/0' then 1 else 0 end as is_entire_network
... from google.compute.firewalls fw, json_array_elements_text(sourceRanges) as rd
... where project = 'testing-project'
... order by name desc, source_range desc;
${inputStr} = Set Variable If "${SQL_BACKEND}" == "postgres_tcp" ${postgresInputStr} ${sqliteInputStr}
${outputStr} = Catenate SEPARATOR=\n
... |---------------|------------------------|--------------|-------------------|
... |${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}id${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}|${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}name${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}|${SPACE}source_range${SPACE}|${SPACE}is_entire_network${SPACE}|
Expand Down