Improve typecasting in mathesar

[Anish9901]

Problem:

• Even though we have an extensive amount of typecasting functions installed on user's database under the mathesar_types namespace (469 to be exact!!!) we still haven't fully covered all typecasts supported by postgres for the types that mathesar currently supports.
• Our backend lacks support for certain types e.g. inet, circle, box, path, line, polygon etc supposedly due to sqlalchemy. So, even though postgres might support typecasting of our supported type to the aformentioned types or vice versa we can't support them.

Potential solution:

• Rely on postgres's typecasting functions as much as possible.
• On user's db install typecasting functions only for our 6 custom types.

A complelling example:

anish=# SELECT 6::bool;
 bool 
------
 t
(1 row)

anish=# SELECT mathesar_types.cast_to_boolean(6);
ERROR:  6 is not a boolean
CONTEXT:  PL/pgSQL function mathesar_types.cast_to_boolean(integer) line 5 at RAISE

anish=# select 'false'::jsonb::bool;
 bool 
------
 f
(1 row)

anish=# SELECT mathesar_types.cast_to_boolean('false'::jsonb);
ERROR:  function mathesar_types.cast_to_boolean(jsonb) does not exist
LINE 1: SELECT mathesar_types.cast_to_boolean('false'::jsonb);
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

Type aliases are confusing:

Are we okay with using type names mentioned in pg_type rather than their synonyms in the backend?

• bigint aka int8
• big serial aka serial8
• bit varying aka varbit
• boolean aka bool
• character aka char
• character varying aka varchar
• double precision aka float8
• int, integer aka int4
• decimal aka numeric
• real aka float4
• smallint aka int2
• smallserial aka serial2
• serial aka serial4
• time with time zone aka timetz
• timestamp with time zone aka timestamptz

(referenced from : https://www.postgresql.org/docs/current/datatype.html)

Getting info about existing typecasting functions provided by postgres:

SELECT source_type.typname AS source, target_type.typname AS target, procedure_name.proname AS function, c.castcontext, c.castmethod
FROM pg_catalog.pg_cast AS c 
JOIN pg_catalog.pg_type AS source_type ON c.castsource = source_type.oid
JOIN pg_catalog.pg_type AS target_type ON c.casttarget = target_type.oid
JOIN pg_catalog.pg_proc AS procedure_name ON c.castfunc = procedure_name.oid
WHERE c.castmethod = 'f' AND source_type.typname NOT LIKE 'pg_%';

    source     |     target     |    function    | castcontext | castmethod 
---------------+----------------+----------------+-------------+------------
 jsonb         | bool           | bool           | e           | f
 int4          | bool           | bool           | e           | f
 varchar       | char           | char           | a           | f
 text          | char           | char           | a           | f
 bpchar        | char           | char           | a           | f
 int4          | char           | char           | e           | f
 bpchar        | name           | name           | i           | f
 varchar       | name           | name           | i           | f
 text          | name           | name           | i           | f
 float8        | int8           | int8           | a           | f
 regcollation  | int8           | int8           | a           | f
 regrole       | int8           | int8           | a           | f
 regnamespace  | int8           | int8           | a           | f
 jsonb         | int8           | int8           | e           | f
 regdictionary | int8           | int8           | a           | f
 regconfig     | int8           | int8           | a           | f
 regtype       | int8           | int8           | a           | f
 regclass      | int8           | int8           | a           | f
 regoperator   | int8           | int8           | a           | f
 regoper       | int8           | int8           | a           | f
 regprocedure  | int8           | int8           | a           | f
 numeric       | int8           | int8           | a           | f
 bit           | int8           | int8           | e           | f
 int2          | int8           | int8           | i           | f
 float4        | int8           | int8           | a           | f
 oid           | int8           | int8           | a           | f
 regproc       | int8           | int8           | a           | f
 int4          | int8           | int8           | i           | f
 float4        | int2           | int2           | a           | f
 int4          | int2           | int2           | a           | f
 int8          | int2           | int2           | a           | f
 float8        | int2           | int2           | a           | f
 jsonb         | int2           | int2           | e           | f
 numeric       | int2           | int2           | a           | f
 bit           | int4           | int4           | e           | f
 int8          | int4           | int4           | a           | f
 jsonb         | int4           | int4           | e           | f
 bool          | int4           | int4           | e           | f
 float8        | int4           | int4           | a           | f
 char          | int4           | int4           | e           | f
 float4        | int4           | int4           | a           | f
 int2          | int4           | int4           | i           | f
 numeric       | int4           | int4           | a           | f
 int2          | regproc        | int4           | i           | f
 int8          | regproc        | oid            | i           | f
 cidr          | text           | text           | a           | f
 name          | text           | text           | i           | f
 bpchar        | text           | text           | i           | f
 char          | text           | text           | i           | f
 inet          | text           | text           | a           | f
 bool          | text           | text           | a           | f
 int8          | oid            | oid            | i           | f
 int2          | oid            | int4           | i           | f
 xid8          | xid            | xid            | e           | f
 text          | xml            | xml            | e           | f
 bpchar        | xml            | xml            | e           | f
 varchar       | xml            | xml            | e           | f
 circle        | point          | point          | e           | f
 lseg          | point          | point          | e           | f
 box           | point          | point          | e           | f
 polygon       | point          | point          | e           | f
 box           | lseg           | lseg           | e           | f
 polygon       | path           | path           | a           | f
 polygon       | box            | box            | e           | f
 circle        | box            | box            | e           | f
 point         | box            | box            | a           | f
 circle        | polygon        | polygon        | e           | f
 path          | polygon        | polygon        | a           | f
 box           | polygon        | polygon        | a           | f
 inet          | cidr           | cidr           | a           | f
 int4          | float4         | float4         | i           | f
 jsonb         | float4         | float4         | e           | f
 int2          | float4         | float4         | i           | f
 int8          | float4         | float4         | i           | f
 float8        | float4         | float4         | a           | f
 numeric       | float4         | float4         | i           | f
 int4          | float8         | float8         | i           | f
 int8          | float8         | float8         | i           | f
 int2          | float8         | float8         | i           | f
 numeric       | float8         | float8         | i           | f
 float4        | float8         | float8         | i           | f
 jsonb         | float8         | float8         | e           | f
 box           | circle         | circle         | e           | f
 polygon       | circle         | circle         | e           | f
 macaddr       | macaddr8       | macaddr8       | i           | f
 int8          | money          | money          | a           | f
 numeric       | money          | money          | a           | f
 int4          | money          | money          | a           | f
 macaddr8      | macaddr        | macaddr        | i           | f
 bool          | bpchar         | text           | a           | f
 cidr          | bpchar         | text           | a           | f
 inet          | bpchar         | text           | a           | f
 bpchar        | bpchar         | bpchar         | i           | f
 name          | bpchar         | bpchar         | a           | f
 char          | bpchar         | bpchar         | a           | f
 varchar       | varchar        | varchar        | i           | f
 bool          | varchar        | text           | a           | f
 cidr          | varchar        | text           | a           | f
 char          | varchar        | text           | a           | f
 inet          | varchar        | text           | a           | f
 bpchar        | varchar        | text           | i           | f
 name          | varchar        | varchar        | a           | f
 timestamptz   | date           | date           | a           | f
 timestamp     | date           | date           | a           | f
 timetz        | time           | time           | a           | f
 time          | time           | time           | i           | f
 timestamp     | time           | time           | a           | f
 timestamptz   | time           | time           | a           | f
 interval      | time           | time           | a           | f
 timestamptz   | timestamp      | timestamp      | a           | f
 timestamp     | timestamp      | timestamp      | i           | f
 date          | timestamp      | timestamp      | i           | f
 date          | timestamptz    | timestamptz    | i           | f
 timestamp     | timestamptz    | timestamptz    | i           | f
 timestamptz   | timestamptz    | timestamptz    | i           | f
 interval      | interval       | interval       | i           | f
 time          | interval       | interval       | i           | f
 time          | timetz         | timetz         | i           | f
 timestamptz   | timetz         | timetz         | a           | f
 timetz        | timetz         | timetz         | i           | f
 int8          | bit            | bit            | e           | f
 int4          | bit            | bit            | e           | f
 bit           | bit            | bit            | i           | f
 varbit        | varbit         | varbit         | i           | f
 jsonb         | numeric        | numeric        | e           | f
 float8        | numeric        | numeric        | a           | f
 numeric       | numeric        | numeric        | i           | f
 float4        | numeric        | numeric        | a           | f
 int2          | numeric        | numeric        | i           | f
 int8          | numeric        | numeric        | i           | f
 int4          | numeric        | numeric        | i           | f
 money         | numeric        | numeric        | a           | f
 int8          | regprocedure   | oid            | i           | f
 int2          | regprocedure   | int4           | i           | f
 int8          | regoper        | oid            | i           | f
 int2          | regoper        | int4           | i           | f
 int8          | regoperator    | oid            | i           | f
 int2          | regoperator    | int4           | i           | f
 int2          | regclass       | int4           | i           | f
 int8          | regclass       | oid            | i           | f
 varchar       | regclass       | regclass       | i           | f
 text          | regclass       | regclass       | i           | f
 int2          | regtype        | int4           | i           | f
 int8          | regtype        | oid            | i           | f
 int8          | regconfig      | oid            | i           | f
 int2          | regconfig      | int4           | i           | f
 int2          | regdictionary  | int4           | i           | f
 int8          | regdictionary  | oid            | i           | f
 int8          | regnamespace   | oid            | i           | f
 int2          | regnamespace   | int4           | i           | f
 int2          | regrole        | int4           | i           | f
 int8          | regrole        | oid            | i           | f
 int8          | regcollation   | oid            | i           | f
 int2          | regcollation   | int4           | i           | f
 int4range     | int4multirange | int4multirange | e           | f
 numrange      | nummultirange  | nummultirange  | e           | f
 tsrange       | tsmultirange   | tsmultirange   | e           | f
 tstzrange     | tstzmultirange | tstzmultirange | e           | f
 daterange     | datemultirange | datemultirange | e           | f
 int8range     | int8multirange | int8multirange | e           | f
(160 rows)

[mathemancer]

I do think we need to do some serious work on our casting functionality, including how to handle casting to/from types we don't explicitly support. However, I think we should avoid getting too deep into that before the beta.

We also need to do some product-level rethinking to figure out how to support this. The reason the current casting functions are somewhat restrictive is to provide a level of safety for the user. In your int -> bool example above, the reason we don't allow casting anything but 1 to true is to avoid losing information for the user. If you only cast 1 -> true and 0 -> false, you can always recover the original integers. If you cast all non-zero integers to true, you lose information on the set {0, 1, 2}. With that said, your jsonb -> bool example should be implemented, insofar as we're supporting jsonb. I hope the distinction is clear.