Extension support for excel

[mdawkins]

I can install and load the excel extension and even see it from querying:
SELECT * FROM duckdb.extensions;

When trying to query a public xlsx file as a test, I get the following:
ERROR: function read_xlsx(unknown) does not exist

Has anyone successfully queried an xlsx file from pg_duckdb, like you would from duckdb?

Thanks,

pg ver: 18.1
pg_duckdb: 1.1.1

[YuweiXiao]

The UDF should be registered on the pg side first. Do sth like this:

CREATE FUNCTION read_xlsx(path text, 
                                        header BOOLEAN DEFAULT NULL,
                                        sheet TEXT DEFAULT NULL,
                                        all_varchar BOOLEAN DEFAULT FALSE,
                                        ignore_errors BOOLEAN DEFAULT FALSE,
                                        range TEXT DEFAULT NULL,
                                        stop_at_empty BOOLEAN DEFAULT NULL,
                                        empty_as_varchar BOOLEAN DEFAULT FALSE)
RETURNS SETOF duckdb.row
SET search_path = pg_catalog, pg_temp
AS 'pg_duckdb.so', 'duckdb_only_function'
LANGUAGE C;

[mdawkins]

@YuweiXiao Thank you, that works! I was able to create the function, mirroring what I saw for read_parquet.

Notes for others:
Patch to add the function on build:
pg_duckdb_read_xlsx_func.patch

Also, had to set
CREATE EXTENSION pg_duckdb;
SELECT duckdb.install_extension('excel');
SELECT duckdb.load_extension('excel');
SELECT duckdb.autoload_extension('excel', true);
SET duckdb.force_execution = true;

Example select
SELECT DD['Screen'], * FROM read_xlsx('https://lsd.blob.core.windows.net/assets/77_asset_file_id_19886_Data_Dictionary_-_Rental_Database_190520_v2.xlsx' , all_varchar := true , sheet := 'Data Dictionary' ) DD LIMIT 10 ;