1

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!

1 Answer 1

0

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.

1
  • 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).
    – Optimum
    Aug 26, 2021 at 18:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.