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)
