Re: function defined (or not), more worries on version 10->14 upgrade - Mailing list pgsql-general

On 4/15/22 10:14, Tom Lane wrote:

Rob Sargent <robjsargent@gmail.com> writes:

The function in question is working but I'm having trouble getting at
it's current definition and the possibility that there are more v10->v14
issues in this database.  The last ERROR: below seems to me "a system
thing"(tm)

Hmm, it looks like \df does not cope well with spaces in the argument
list:

regression=# \df genome_threshold_mono(text,text,double)
                        List of functions
  Schema | Name | Result data type | Argument data types | Type
--------+------+------------------+---------------------+------
(0 rows)

regression=# \df genome_threshold_mono(text,text, double)
ERROR:  invalid regular expression: parentheses () not balanced

It's sending the server a bogus pattern in the second case.
I've not looked at the code yet, but this does seem like a
psql (not server) bug.

Seems it also has another white space problem:

\df upc_check_digit()
                              List of functions
 Schema |      Name       | Result data type  |  Argument data types  | Type
--------+-----------------+-------------------+-----------------------+------
 public | upc_check_digit | character varying | upc character varying | func

\df upc_check_digit ()
                       List of functions
 Schema | Name | Result data type | Argument data types | Type
--------+------+------------------+---------------------+------
(0 rows)

\df upc_check_digit(varchar)
                       List of functions
 Schema | Name | Result data type | Argument data types | Type
--------+------+------------------+---------------------+------
(0 rows)

\df upc_check_digit (varchar)
                              List of functions
 Schema |      Name       | Result data type  |  Argument data types  | Type
--------+-----------------+-------------------+-----------------------+------
 public | upc_check_digit | character varying | upc character varying | func
(1 row)

barnard=# \df genome_threshold_mono(text,text,double precision);
ERROR:  invalid regular expression: parentheses () not balanced
barnard=# \df genome_threshold_mono(text,text,double precision);
ERROR:  invalid regular expression: parentheses () not balanced
barnard=# \df genome_threshold_mono(text,text,float);
                       List of functions
 Schema | Name | Result data type | Argument data types | Type
--------+------+------------------+---------------------+------
(0 rows)

barnard=# \df genome_threshold_mono(text,text,'double precision');
                       List of functions
 Schema | Name | Result data type | Argument data types | Type
--------+------+------------------+---------------------+------
(0 rows)

barnard=# \df genome_threshold_mon*
                                                 List of functions
 Schema |         Name          | Result data type |                  Argument data types                   | Type
--------+-----------------------+------------------+--------------------------------------------------------+------
 public | genome_threshold_mono | uuid             | pbs_name text, genome_name text                        | func
 public | genome_threshold_mono | uuid             | pbs_name text, genome_name text, conf double precision | func
(2 rows)

barnard=# \df genome_threshold_mono (text,text,double precision);
                                                 List of functions
 Schema |         Name          | Result data type |                  Argument data types                   | Type
--------+-----------------------+------------------+--------------------------------------------------------+------
 public | genome_threshold_mono | uuid             | pbs_name text, genome_name text                        | func
 public | genome_threshold_mono | uuid             | pbs_name text, genome_name text, conf double precision | func
(2 rows)

\df: extra argument "(text,text,double" ignored
\df: extra argument "precision);" ignored
barnard=# \df genome_threshold_mono (text,text,float);
                                                 List of functions
 Schema |         Name          | Result data type |                  Argument data types                   | Type
--------+-----------------------+------------------+--------------------------------------------------------+------
 public | genome_threshold_mono | uuid             | pbs_name text, genome_name text                        | func
 public | genome_threshold_mono | uuid             | pbs_name text, genome_name text, conf double precision | func
(2 rows)

\df: extra argument "(text,text,float);" ignored

barnard=# \sf shoc.pvr(shoc.segment, float)
CREATE OR REPLACE FUNCTION shoc.pvr(seg shoc.segment, plus double precision DEFAULT 0.0)
 RETURNS double precision
 LANGUAGE sql
AS $function$
  select ((1.0*seg.events_equal)+seg.events_greater+plus)/((1.0*seg.events_less)+seg.events_equal+seg.events_greater+plus)::float as result;
$function$

            regards, tom lane