COPY pg_view TO s3 fails with missing scalar function

[laogao]

What happens?

When I try to COPY (SELECT * FROM some_pg_view_built_with_pg_functions) TO 's3://...', it fails with the following error:

ERROR:  (PGDuckDB/DuckDBQuery) Catalog Error: Scalar Function with name to_char does not exist!
Did you mean "to_hours"?

Copy to local path e.g. COPY (SELECT * FROM some_pg_view_built_with_pg_functions) TO '/tmp/some.csv' works, though.

To Reproduce

CREATE EXTENSION IF NOT EXISTS pg_duckdb;
CREATE VIEW pg_view AS (SELECT to_char(NOW(), 'YYYY-MM-DD HH24:MI:SS'::text) AS ts);
COPY (SELECT * FROM pg_view) TO '/tmp/pg_view.csv';
COPY (SELECT * FROM pg_view) TO 's3://media/pg_view.csv';

OS:

Linux

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

0.1.0

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

17.0

Hardware:

No response

Full Name:

Sean Gao

Affiliation:

Palan DataRx

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?

Yes

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]

Currently you can only use functions in queries that also exist in DuckDB, for queries that require duckdb execution. This is a known issue. It also does not depend on using a view. You get the same error when simply putting your query in the COPY statement directly.

> COPY (SELECT to_char(NOW(), 'YYYY-MM-DD HH24:MI:SS'::text)) TO 's3://media/pg_view.csv';
ERROR:  XX000: (PGDuckDB/DuckdbUtilityHook) Catalog Error: Scalar Function with name to_char does not exist!
Did you mean "to_hours"?