On Mon, Feb 16, 2009 at 03:21:12PM +0100, Pavel Stehule wrote:
> 2009/2/16 Sam Mason <sam@samason.me.uk>:
> > On Mon, Feb 16, 2009 at 02:35:54PM +0100, Pavel Stehule wrote:
> >> attachment contains module that transform every empty string to null.
> >
> > Why would anyone ever want to do this? This would appear to break all
> > sorts of things in very non-obvious ways:
>
> I agree, so this behave is strange - but Oracle does it.
>
> so normal query in Oracle for empty value looks like
>
> select * from people where surname is null;
>
> and some application expect transformation from '' to null.
>
> http://www.thunderguy.com/semicolon/2003/04/26/oracle-empty-string-null/
that's pretty grim!
<rant> I'd agree with the comment saying "A string variable that can't be set empty is like a number variable that
can'tbe set zero".
Oracle have just thrown out (or, at best, rewritten) the inductive base case for strings. For numbers you (logically)
startwith a Zero and a Succ (successor function) and model numbers as an arbitrary number of applications of Succ to
Zero("2" is (Succ (Succ Zero))). For strings, you start with an empty string and an append function ("hi" being (Append
(Append'' \h) \i)).
</rant>
> so these modules (decode, oraemptystr) decrease differences between
> PostgreSQL and Oracle.
wouldn't it be better/easier to extend something like pgpool to
transform Oracle style SQL code to PG style code? You'd certainly
be able to get it more complete in reasonable amounts of time, but
performance would suffer when you went to look up table definitions to
check the types of various things.
-- Sam http://samason.me.uk/