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!