Thread: Controlling the usage of a user-defined cast

Hi,

When a user-defined CAST is created, it has the ability to break behavior of built-in

casts that could  be performed implicitly, i.e. without a cast defined in pg_cast.

Below is a simple example of a user-defined text to numeric cast that changes the behavior of

such a type conversion.

postgres=# create table test ( id text ); insert into test values ('1.09');

CREATE TABLE

INSERT 0 1

postgres=# 

postgres=# select id::numeric from test ;

  id  

------

 1.09

(1 row)

postgres=# 

postgres=# CREATE OR REPLACE FUNCTION text_to_numeric(t text) RETURNS numeric AS $$

postgres$# BEGIN

postgres$#     RETURN t::float::numeric(10, 0);

postgres$# END;

postgres$# $$ LANGUAGE plpgsql;

CREATE FUNCTION

postgres=# create cast(text as numeric) with function text_to_numeric(text) AS IMPLICIT;

CREATE CAST

postgres=# 

postgres=# select id::numeric from test ;                                                                                                      

id 

----

  1

(1 row)

In the above example, PostgreSQL is able to perform a text to numeric cast without an

explicit cast in pg_cast. Because a cast does not exist in pg_cast, a user ( with ownership to the source or

target type ) is able to create a new cast that will alter the built-in behavior.

This cast is now scoped to the entire database and the behavior intended by the creator of

the cast may not be the behavior desired for all applications. Also, if the cast returns erroneous

results, the scope of the error is now for the entire database.

There is currently no way to prevent the usage of a user-defined cast. Should there be one?

One idea I have been thinking about is to control this behavior via a GUC, but there may be

other ways to explore to handle this.

Any thoughts?

Regards,

Sami Imseih

Amazon Web Services (AWS)