cli: \df command to list or show all user defined functions

[michae2]

It would be nice to have a dedicated command to show all user defined functions in a database or schema.

• PostgreSQL's psql has the \df command.

  michae2=# \df
                         List of functions
   Schema | Name | Result data type | Argument data types  | Type
  --------+------+------------------+----------------------+------
   public | f2   | integer          |                      | func
   public | g    | integer          | a integer, b integer | func
   public | g    | integer          | i integer            | func
   public | m    | character        | f double precision   | func
   public | m    | character        | i integer            | func
   public | p    | boolean          | i integer            | func
  (6 rows)
  

• MySQL has the SHOW FUNCTION STATUS statement.

We do include user-defined functions in the output of \hf but this only gives the function name, not any additional information:

[email protected]:26257/defaultdb> CREATE FUNCTION z () RETURNS INT IMMUTABLE LANGUAGE SQL AS $$ SELECT 1 $$;
CREATE FUNCTION


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

[email protected]:26257/defaultdb> \hf
                            function
----------------------------------------------------------------
  _st_contains
  _st_containsproperly
  _st_coveredby
...
  with_max_staleness
  with_min_timestamp
  xor_agg
  z
(919 rows)


Time: 20ms total (execution 17ms / network 3ms)

[email protected]:26257/defaultdb> \hf z
no help available for "z".
Try \hf with no argument to see available help.

As a workaround, we can query pg_catalog.pg_proc:

[email protected]:26257/defaultdb> SELECT proname, prosrc FROM pg_catalog.pg_namespace n JOIN pg_catalog.pg_proc ON pronamespace = n.oid WHERE nspname = 'public';
  proname |  prosrc
----------+------------
  z       | SELECT 1;
(1 row)


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

But this is a little annoying, and it would be nice to match psql.

This is on v22.2.0-alpha.3-550-g099da40b73 (current tip of master).

Jira issue: CRDB-19851

Epic CRDB-23454

[rafiss]

The \df cli command and the SHOW command would be implemented differently, so maybe this should be two separate issues. ccing @chengxiong-ruan since it seems related to #88773 and #85656

[chengxiong-ruan]

Thanks for filing the issue!
Yeah, it's definitely helpful to have the \df command to list all functions, and also \df some_func_name
Agreed that SHOW FUNCTION STATUS should be a separate issue. Though, I'm not sure if we need it by looking at some example outputs of this mysql tutorial

[michae2]

Thanks for filing the issue! Yeah, it's definitely helpful to have the \df command to list all functions, and also \df some_func_name Agreed that SHOW FUNCTION STATUS should be a separate issue. Though, I'm not sure if we need it by looking at some example outputs of this mysql tutorial

Yeah, let's forget SHOW FUNCTION STATUS for now and just make this issue about \df 👍

[rafiss]

This is how pgcli implements \df:

            SELECT  n.nspname as "Schema",
                    p.proname as "Name",
                    pg_catalog.pg_get_function_result(p.oid)
                      as "Result data type",
                    pg_catalog.pg_get_function_arguments(p.oid)
                      as "Argument data types",
                     CASE
                        WHEN p.prokind = 'a' THEN 'agg'
                        WHEN p.prokind = 'w' THEN 'window'
                        WHEN p.prorettype = 'pg_catalog.trigger'::pg_catalog.regtype
                            THEN 'trigger'
                        ELSE 'normal'
                    END as "Type" 
            FROM    pg_catalog.pg_proc p
                    LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace
                            
            WHERE   pg_catalog.pg_function_is_visible(p.oid)  AND n.nspname <> 'pg_catalog'
                   AND n.nspname <> 'information_schema'  ORDER BY 1, 2, 4

and this is how psql implements \df:

SELECT n.nspname as "Schema",
  p.proname as "Name",
  pg_catalog.pg_get_function_result(p.oid) as "Result data type",
  pg_catalog.pg_get_function_arguments(p.oid) as "Argument data types",
 CASE p.prokind
  WHEN 'a' THEN 'agg'
  WHEN 'w' THEN 'window'
  WHEN 'p' THEN 'proc'
  ELSE 'func'
 END as "Type"
FROM pg_catalog.pg_proc p
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace
WHERE pg_catalog.pg_function_is_visible(p.oid)
      AND n.nspname <> 'pg_catalog'
      AND n.nspname <> 'information_schema'
ORDER BY 1, 2, 4;

We should be able to do something similar. It can be added similarly to

cockroach/pkg/cli/clisqlshell/sql.go

Line 1292 in bfbd07b

case `\dt`: