Help understanding behavior of try_cast() with numeric strings

Question

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!

Answer 1

Because %s omits the quotes. You can see this by trying format() interactively:

testdb=# select format('%s', '50.0') as "%s", format('%L', '50.0') as "%L";
 %s | %L 
------+--------
 50.0 | '50.0'
(1 row)

I don't think there's any way of doing this within try_cast(); as I see it, it's doing the right thing. The string '50.0' is legitimately not a representation of an integer, so casting to numeric first is correct.

• Optimum: Oh, ok! After reading this, I tried SELECT 192.168::cidr and got "Cannot cast type numeric to cidr", so that pretty much clears it up. That's sad, but a very crucial detail! I'll go ahead and mark this as the answer (now to figure out how to make a good warning label).