/*
    # Why this intermediary function is required
    Just like the 'CREATE TYPE' statement, a 'CREATE CAST' statement with 'WITH FUNCTION' 
    - expects a SQL function that is already defined in the database (stored in pg_proc).
    - it cannot reference a C function directly with MODULE_PATHNAME.
    - requires signature matching. The cast function should have the exact signature for the conversion.
      For CAST(text as bs_date), the function needs:  
      conversion_function(text) RETURNS bs_date
    In our case, we need a function that takes in 'text' as an argument and returns 'bs_date'
    
    This is why:
    - we cannot makes use of the 'bs_date_in' function we have above defined in our 'types.sql' file because 
      it is a type INPUT function, which expects a 'cstring' argument
    - also, we cannot make use of 'MODULE_PATHNAME', 'bs_date_in' here because we need a 'WITH FUNCTION' statement here 
      which expects a function

    Role of the intermediary function:
    - takes text 
    - make use of a C function that correctly handles 'text' (varlena) input using the text_to_cstring Postgres function
*/
CREATE FUNCTION text_to_bs_date(text) RETURNS bs_date
AS 'MODULE_PATHNAME', 'text_to_bs_date'
LANGUAGE C IMMUTABLE STRICT;

CREATE CAST (text as bs_date) 
WITH FUNCTION text_to_bs_date(text)
AS IMPLICIT;
/*
    - AS IMPLICIT means the cast can be invoked implicitly in any context, including general expressions and assignments. 
    - AS ASSIGNMENT means the cast can be invoked implicity in assignment contexts (e.g., when inserting into a table column)
    - Not specifying any of the two will make the cast 'Explicity only'
      Meaning, the cast will only be used during explicity cast operations like the following:
      SELECT '2081/01/12'::bs_date;
      SELECT CAST('2081/01/12' AS bs_date);
*/