Doesn't work when statement does not end with ORDER BY

[char101]

Hi,

I tested this extension with a simple sql and I found two bugs

1. It does not work if the previous statement does not ends with ORDER BY
2. It causes error with bind parameter

Example:

import duckdb
import polars as pl

df = pl.DataFrame({'a': [1, 2, 3]})

duckdb.execute('load psql')

duckdb.execute('from df select * where a >= 2 |> order by a desc')
#     ~~~~~~~~~~~~~~^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
# duckdb.duckdb.CatalogException: Catalog Error: Scalar Function with name |>__postfix does not exist!
# Did you mean "!__postfix"?
#
# LINE 1: from df select * where a >= 2 |> order by a desc

duckdb.execute('from df select * where a >= 2 order by a asc |> order by a desc')
# OK

duckdb.execute('from df select * where a >= ? order by a asc |> order by a desc', [2])
# ~~~~~~~~~~~~~~^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
# duckdb.duckdb.InvalidInputException: Invalid Input Error: Parameter argument/count mismatch, identifiers of the excess parameters: 1

duckdb version: 1.2.0

[ywelsch]

Hi @char101, these are limitations with the syntactic transformation described in https://github.com/ywelsch/duckdb-psql?tab=readme-ov-file#how-does-it-work
The PSQL extension is not a proper DuckDB parser.

In the first case (from df select * where a >= 2 |> order by a desc) I think this is recognized as a proper DuckDB SQL expression, i.e. DuckDB does not call the PSQL parser extension. A crude hack would be to add more brackets to the statement, e.g. (from df select * where a >= 2) |> order by a desc.

I don't see a simple way to solve these issues without making pipes a first-class citizen in DuckDB (rather than being relegated to an extension).