Invalid SQL generated with arrays and TypeRef

[tanadeau]

Description

Invalid SQL generated from functions taking TypeRef when given a string for an array type.

        let (sql, _) = Query::select()
            .column(Asterisk)
            .from_function(
                PgFunc::json_table(
                    Expr::val(r#"{"foo": "some value", "bar": ["x", "y", "z"]}"#),
                    "$",
                )
                .column("foo", "text")
                .path("$.foo")
                .build_column()
                .column("bar", "text[]")
                .path("$.bar")
                .wrapper(WrapperKind::WithUnconditional)
                .empty_array_on_empty()
                .error_on_error()
                .build_column()
                .build(),
                "stuff",
            )
            .build_sqlx(PostgresQueryBuilder);

produces a SQL fragment like:

SELECT * FROM JSON_TABLE('{"foo": "some value", "bar": ["x", "y", "z"]}', '$' COLUMNS (foo "text" PATH '$.foo', bar "text[]" PATH '$.bar' WITH UNCONDITIONAL WRAPPER EMPTY ARRAY ON EMPTY ERROR ON ERROR)) AS "stuff"

which gives the following error: ERROR: type "text[]" does not exist.

Removing the quotes around "text[]" in the generated SQL fixes the issue.

Steps to Reproduce

See above.

Expected Behavior

Valid SQL

Actual Behavior

Invalid SQL when using arrays as they are quoted.

Reproduces How Often

Always.

Versions

sea-query: 1.0.0-rc.22
PostgreSQL: 17.4
OS: RHEL8

Additional Information

[Huliiiiii]

Slightly off-topic question: do you prefer a flat builder or a nested builder?

[tanadeau]

I would prefer the each column would be its own builder so the formatting would be nicer. Like a .column(JsonTableColumn::new(“foo”, “text”).path(…))