Pivot error

[JC1738]

What happens?

-- Reviewed https://github.com/duckdb/pg_duckdb/blob/main/docs/gotchas_and_syntax.md#when-to-use-duckdbquery

-- This Works on my pgduckdb/pgduckdb:17-v1.0.0 instance
SELECT * FROM duckdb.raw_query('

PIVOT "Order"
ON "StoreId"
USING SUM("Total")/100
GROUP BY status

');

-- This fails
SELECT * FROM duckdb.query($$
PIVOT "Order"
ON "StoreId"
USING SUM("Total")/100
GROUP BY status
$$);

-- Query 1 ERROR at Line 1: : ERROR: (PGDuckDB/CreatePlan) Prepared query returned an error: Parser Error: Expected a single SELECT statement

To Reproduce

SELECT * FROM duckdb.query('$$

    PIVOT "Order"
    ON "StoreId"
    USING SUM("Total")/100
    GROUP BY status

$$');   

OS:

Docker (running on MacOS)

pg_duckdb Version (if built from source use commit hash):

pgduckdb/pgduckdb:17-v1.0.0 i

Postgres Version (if built from source use commit hash):

17

Hardware:

N/A

Full Name:

Jim Castillo

Affiliation:

RepairWise

What is the latest build you tested with? If possible, we recommend testing with the latest nightly build.

I have tested with a stable release

Did you include all relevant data sets for reproducing the issue?

No - I cannot share the data sets because they are confidential

Did you include all code required to reproduce the issue?

• Yes, I have

Did you include all relevant configuration (e.g., CPU architecture, Linux distribution) to reproduce the issue?

• Yes, I have

[JelteF]

Sadly that's because PIVOT is a special type of query in DuckDB that's not allowed to be used in DuckDB its query function. I think it could be possible to create a stored procedure/function in pg_duckdb that would allow doing this. Basically an improved version of duckdb.raw_query that actually outputs the result. You wouldn't be able to choose what columns to select from it though. i.e. you could only do SELECT * FROM duckdb.raw_query2(...) not SELECT a, b FROM duckdb.raw_query2(...).