This issue tracks adding basic user-defined scalar SQL function support. A scalar user-defined function is a user-defined function that only uses scalar projections in a SELECT.

For example,

create function f (a int, b int) returns int as 'select a+b' language sql;

There are quite a few modifiers that can be specified on CREATE FUNCTION according to the PostgreSQL documentation: https://www.postgresql.org/docs/13/sql-createfunction.html

Checklist for things that we'll want to make sure of by the time this is finished:

• CREATE FUNCTION
• CREATE OR REPLACE FUNCTION
• DROP FUNCTION
• UDFs should be present in pg_proc and other such metadata tables
• UDFs in views should get their dependencies tracked properly
• cached plans need to notice when UDFs are modified
• UDFs in computed columns should get their dependencies tracked properly
• Make sure that user-defined functions with custom types are going to be okay
• Make sure that ::regproc casts work properly both to and from oid/name. See sql,tree: make ::REGPROC cast efficient #61211.

Additionally, it turns out that to properly do name resolution for user-defined functions with overloads, there is significant extra complexity. We can't simply store each overload in the namespace table with its type parameters as part of the name, because that would require an uncachable range scan on every resolution to a given user-defined function.

So, what we need to do is to implement a 2-level descriptor tree for user-defined functions: the parent contains the name of the UDF, and the children contain the overload types and definition (and privileges, dependencies, etc). This is fairly irritating because leasing a user-defined function now requires pulling in all of the children overloads, and leasing them as well.

We'd like to follow the pattern that was used for leasing tables that contain user-defined types, which have a similar problem. The problem is solved with a special cache of user-defined types that is implemented in the pkg/sql/catalog/hydratedtables package.

Vague todo list:

• flesh out the func overload descriptor protobuf
• add logic to create both the parent and child upon CREATE FUNCTION
• generalize the hydratedtables package to also work for func overload descriptors
• thread a func overload descriptor cache into the table collection to cache the func descriptors upon retrieval of the parent function descriptor

Jira issue: CRDB-11401