Notes:

The "unknown function" error is encountered at this call to GetBuiltinFuncDefinitionOrFail:

To avoid the error, we need to pass in a non-nil resolver to (*ResolvableFunctionReference).Resolve when this function is called by the optimizer code:

The resolver is nil at that point because semaCtx.FunctionResolver is not set. After digging through some history, I found that afe78f9 modified the code so that we intentionally do not set the FunctionResolver.

Using a non-nil resolver is blocked by a different task: we'd need to update the CREATE VIEW code so that we rewrite function references to refer by OID rather than by name. This rewrite functionality is not strictly needed, but can be done once the rest of this issue is completed. Previously, we added support for referencing functions by OID in #83231. This is the area of the code where we'd do the rewrite:

That work is captured under #87699, but we can leave this issue open since this one is specific to views, but that one is more general.

While resolving this issue, we should add tests to make sure the view definition is serialized correctly, and also verify that dropping a function that is referenced by a view is blocked.

We also need to update this area of the code to add the dependency on the function to desc.DependsOnFunctions:

In order to do that, the function dependencies need to be correctly set by the optimizer in this code:

The SQL Queries team is best equipped for that task.

Speaking to @rafiss, his thoughts are:

i am realizing that the SQL Foundations part of this is only needed if we want to support renaming the function while it's referenced by a view. however, we don't even support that for functions referencing other functions:

root@localhost:26257/defaultdb> CREATE FUNCTION f() RETURNS BOOL LANGUAGE SQL AS $$ SELECT true; $$;;
CREATE FUNCTION

root@localhost:26257/defaultdb> CREATE FUNCTION g() RETURNS BOOL LANGUAGE SQL AS $$ SELECT f(); $$;;
CREATE FUNCTION

root@localhost:26257/defaultdb> alter function f rename to t;
ERROR: cannot rename function "f" because other functions ([defaultdb.public.g]) still depend on it
SQLSTATE: 0A000
HINT: You have attempted to use a feature that is not yet implemented.
See: https://go.crdb.dev/issue-v/83233/v25.2

root@localhost:26257/defaultdb> drop function f;
ERROR: cannot drop function "f" because other objects ([defaultdb.public.g]) still depend on it
SQLSTATE: 2BP01

We could have that same behavior for views. in that case, all the work here is for Queries.

@rytaft @michae2 I am moving this issue to SQL Queries based on Rafi's latest comment. We can triage it in our next triage.

re-opening for backport

does this also depend on #146475?

No, #146475 depends on this. I think we should make sure to include #146475 in the release as well, otherwise we will need to write a migration at some point to convert all the view queries that reference UDFs.

Assigning to @rafiss since @DrewKimball's PR is merged now.

As per discussion in the SQL Foundations weekly, we will consider this issue resolved.

The remaining work to use by-ID references for UDFs used by views is tracked in #87699 and will be done in a later release. At that time, we will need to add a migration to rewrite any existing UDF references that are in descriptors.