`SHOW CREATE FUNCTION` doesn't produce output that is portable to other databases

Describe the problem

The DDL produced by SHOW CREATE FUNCTION is not correct. If the function references a table name, we will use a 3-part name for the table, which isn't portable if you want to apply the DDL to another database.

To Reproduce

Here are the steps:

demo@127.0.0.1:26257/demoapp/movr> use defaultdb;
SET

Time: 6ms total (execution 6ms / network 0ms)

demo@127.0.0.1:26257/demoapp/defaultdb> create table t1 (c1 int);
CREATE TABLE

Time: 9ms total (execution 8ms / network 0ms)

demo@127.0.0.1:26257/demoapp/defaultdb> create function f() returns int volatile language SQL AS $$
                                     -> SELECT * FROM t1;
                                     -> $$;
CREATE FUNCTION

Time: 26ms total (execution 21ms / network 4ms)

demo@127.0.0.1:26257/demoapp/defaultdb> show create function f;
  function_name |              create_statement
----------------+---------------------------------------------
  f             | CREATE FUNCTION public.f()
                |     RETURNS INT8
                |     VOLATILE
                |     NOT LEAKPROOF
                |     CALLED ON NULL INPUT
                |     LANGUAGE SQL
                |     AS $$
                |     SELECT t1.c1 FROM defaultdb.public.t1;
                | $$
(1 row)

Time: 15ms total (execution 14ms / network 0ms)

Notice that the SELECT in the function refers to the table as defaultdb.public.t1.

If you try and see the DDL for the create with SHOW CREATE, the database name is not included with the table, as you would expect.

demo@127.0.0.1:26257/demoapp/defaultdb> show create table t1;
  table_name |                      create_statement
-------------+--------------------------------------------------------------
  t1         | CREATE TABLE public.t1 (
             |     c1 INT8 NULL,
             |     rowid INT8 NOT VISIBLE NOT NULL DEFAULT unique_rowid(),
             |     CONSTRAINT t1_pkey PRIMARY KEY (rowid ASC)
             | )
(1 row)

Time: 20ms total (execution 20ms / network 0ms)

If you try to create a function in another database that refers to the t1, then that is blocked. So, I don't think we need to include the table as a 3-part name.

demo@127.0.0.1:26257/demoapp/defaultdb> use movr;
SET

Time: 1ms total (execution 1ms / network 0ms)

demo@127.0.0.1:26257/demoapp/movr> create function f() returns int volatile language SQL AS $$
                                -> SELECT * FROM defaultdb.public.t1;
                                -> $$;
ERROR: dependent relation t1 cannot be from another database
SQLSTATE: 0A000

Expected behavior I would expect to see the function output to look like this:

demo@127.0.0.1:26257/demoapp/defaultdb> show create function f;
  function_name |              create_statement
----------------+---------------------------------------------
  f             | CREATE FUNCTION public.f()
                |     RETURNS INT8
                |     VOLATILE
                |     NOT LEAKPROOF
                |     CALLED ON NULL INPUT
                |     LANGUAGE SQL
                |     AS $$
                |     SELECT t1.c1 FROM public.t1;
                | $$
(1 row)

This would make it consistent with postgres pg_dump output:

$> pg_dump -U postgres test --schema-only;

--
-- Name: f(); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION public.f() RETURNS integer
    LANGUAGE sql
    AS $$
SELECT * FROM t1;
$$;

ALTER FUNCTION public.f() OWNER TO postgres;

SET default_tablespace = '';

SET default_table_access_method = heap;

--
-- Name: t1; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.t1 (
    c1 integer
);

Environment:

CockroachDB version: CockroachDB CCL v24.2.0-alpha.00000000-dev (darwin arm64, built , go1.22.3 X:nocoverageredesign)

Jira issue: CRDB-39674

cockroachdb / cockroach

`SHOW CREATE FUNCTION` doesn't produce output that is portable to other databases #125936