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 %L to %s:
EXECUTE format('SELECT %s::%s', $1, pg_typeof(_out))
And 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!
