Support for calling PG-only scalar functions

[JelteF]

Description

We currently only support calling scalar functions that exist in DuckDB from DuckDB. There are (at least) three situations worth considering here:

1. Converting a PG-only type (e.g. from an extension), to a type that duckdb understands. e.g. taking a PG spatial type, and converting it to a text representation.
2. Converting a type that DuckDB understands to a PG-only type. Converting a text representation of a spatial type, back to the actual spatial type.
3. Converting a type that DuckDB understands to another type that duckdb understands. E.g. a simple plgpgsql UDF that does some date truncation, if the date is before the year 2000.

None of these situations are trivial to implement afaict. But number 3 is probably the first usecase we should tackle. Afaict what we need to do to achieve 3 is:

1. Our GetCatalogEntry should return catalog entries for functions.
2. The functions that those return need to call the underlying postgres functions, by first converting the DuckDB arguments to postgres representation, and then the result back to duckdb representation.

Then once we have this, situation 1 and 2 could be built on top of that support, by adding a new type to DuckDB that represents a Postgres type that's fully opaque to DuckDB, i.e. it would simply store the Postgres bytes. The only operations possible on such a vector would be:

• "cast to string" (because all postgres types support that)
• call a Postgres-only function on it
• return it to postgres

Related to #483 (which is about adding support for PG-only set-returning functions)

[rcronin]

Would this resolve this error?

org.postgresql.util.PSQLException: ERROR: (PGDuckDB/CreatePlan) Prepared query returned an error: 'Catalog Error: Scalar Function with name to_char does not exist! Did you mean "to_hours"?

[JelteF]

@rcronin yes, it would. This would fall under situation number 3 (the ones we should solve the first). For now as a workaround you can use DuckDB its strftime and strptime functions to do date formatting instead (on the main branch only for now, an official release will probably follow in a few weeks).