Erwin made a great, generic
try_cast function for postgres (copied below). I'm interested in using it for an EAV schema where attributes are stored with a type field. I can use a similar function (that returns a bool) as a trigger that tests if a value string can be cast to the corresponding datatype to add some type-safety.
CREATE OR REPLACE FUNCTION try_cast(_in text, INOUT _out ANYELEMENT) AS $func$ BEGIN EXECUTE format('SELECT %L::%s', $1, pg_typeof(_out)) INTO _out; EXCEPTION WHEN others THEN -- do nothing: _out already carries default END $func$ LANGUAGE plpgsql;
When testing it though (in PG11), I found that it fails if casting a decimal number string to an integer type (smallint, int, and bigint) -
try_cast('50.0', NULL::int). I'm guessing this is because postgres can't intermediately cast a string to a numeric/decimal type before casting to an integer (which makes sense).
try_cast('50.0', NULL::numeric) does work, so I might just call that good enough.
However, I played with this line:
EXECUTE format('SELECT %L::%s', $1, pg_typeof(_out))
And changed the
EXECUTE format('SELECT %s::%s', $1, pg_typeof(_out))
try_cast('50.0', NULL::<integer-type>) works without issue. The downside is that other types, like
macaddr/cidr, fail to cast when they worked before
try_cast('192.168'::text, NULL::cidr) -- returns null -- but 'SELECT '192.168'::cidr' works
I'm really curious as to why the
%s fixes the numeric-type casting and breaks others, and if there's any way to improve the function to satisfy this edge-case. If not, I can live with
numeric, but I'm worried for others using this function in the future without understanding its limitations.
Thanks for any help or ideas!