Greg Stark <gsstark(at)mit(dot)edu> wrote:
> Given that pg_typeof() is a relatively new and pg-specific piece of
> machinery how did this bite you on on your conversion to Postgres
> some years ago?
It wasn't the use of pg_typeof which caused us problems, but the types
the example demonstrated. Primarily that bit us when our framework
substituted values from the application or user selection windows into
complex queries, with the result that a coalesce of two NULLs was used
in a context where numbers or dates were expected.
Our initial hack, which got us up and running fine, was to modify the
JDBC driver to substitute a bare NULL for the COALESCE of two NULLs in
the JDBC compatibility code which mapped to COALESCE. As a longer-
term, less fragile fix we pushed type information deeper into the code
making the JDBC requests and had it explicitly wrap a NULL with a
CAST. Still, it rates pretty high on my astonishment scale that a
COALESCE of two untyped NULLs (or for that matter, any two values of
unknown type) returns a text value.
It's one of those things which apparently seems unsurprising for those
viewing the product from the inside out.
-Kevin