Обсуждение: Orafce concat operator
Hello list, The Orafce compatibility package doesn't seem to have operators defined (looked in the sql load file). The function I'm specifically interested in, is Oracle's concatenation that regards a NULL as the empty string and hence returns 'the other value'. This in contrast with Pg's || that returns NULL if either of the operands is NULL. The Orafce package contains a concat function with Oracle behaviour, however an operator is missing. Having an associative operator has benefits over having only a function, since that would make translating expressions like 'monkey' || 'nut' || NULL easy. What about adding something like operator ||| in the orafce package for concat? Regards, Yeb Havinga
2010/2/10 Yeb Havinga <yebhavinga@gmail.com>: > Hello list, > > The Orafce compatibility package doesn't seem to have operators defined > (looked in the sql load file). The function I'm specifically interested in, > is Oracle's concatenation that regards a NULL as the empty string and hence > returns 'the other value'. This in contrast with Pg's || that returns NULL > if either of the operands is NULL. The Orafce package contains a concat > function with Oracle behaviour, however an operator is missing. > > Having an associative operator has benefits over having only a function, > since that would make translating expressions like 'monkey' || 'nut' || NULL > easy. > > What about adding something like operator ||| in the orafce package for > concat? no, it could be confusing and it isn't enough, because it isn't only || or concat problem. On Oracle empty string is equal to NULL and NULL is equal to empty string. example: '' is null, length('') http://www.thunderguy.com/semicolon/2003/04/26/oracle-empty-string-null/ so we are not able emulate this behave. Regards Pavel Stehule > > Regards, > Yeb Havinga > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
Pavel Stehule wrote: >> What about adding something like operator ||| in the orafce package for >> concat? >> > > no, it could be confusing and it isn't enough, because it isn't only > || or concat problem. On Oracle empty string is equal to NULL and NULL > is equal to empty string. > > example: '' is null, length('') > > http://www.thunderguy.com/semicolon/2003/04/26/oracle-empty-string-null/ > > so we are not able emulate this behave. > Hi Pavel, Thanks for your quick reply. What you say is entirely true, however I'm not sure if you completely understood my question, so please do not be offended if I try to elaborate it a bit: I did not ask for full '' = NULL emulation, only for an operator to match the concat function, so that code conversion with repetetive concats are easier (string || string || string etc) which occur frequently. Ofcourse a translator can be made that converst that to concat(string,concat(string,concat(string etc))), however that's confusing too in a different way. So imho the only problem is to think of an operator that somehow resembles || so the programmer recognizes a concat, but also adds something so the programmer recognizes: not strict. What about ||+ ? And then, at the conversion of e.g. (string || string || string) IS NULL, confusion arises, but this is not due to the concat, but more to the IS NULL clause together with the strange '' = null, and that must be handled otherwise. So the operator would speed up part of the code conversion. regards, Yeb Havinga
2010/2/10 Yeb Havinga <yebhavinga@gmail.com>: > Pavel Stehule wrote: >>> >>> What about adding something like operator ||| in the orafce package for >>> concat? >>> >> >> no, it could be confusing and it isn't enough, because it isn't only >> || or concat problem. On Oracle empty string is equal to NULL and NULL >> is equal to empty string. >> >> example: '' is null, length('') >> >> http://www.thunderguy.com/semicolon/2003/04/26/oracle-empty-string-null/ >> >> so we are not able emulate this behave. >> > > Hi Pavel, > > Thanks for your quick reply. What you say is entirely true, however I'm not > sure if you completely understood my question, so please do not be offended > if I try to elaborate it a bit: > > I did not ask for full '' = NULL emulation, only for an operator to match > the concat function, so that code conversion with repetetive concats are > easier (string || string || string etc) which occur frequently. Ofcourse a > translator can be made that converst that to > concat(string,concat(string,concat(string etc))), however that's confusing > too in a different way. So imho the only problem is to think of an operator > that somehow resembles || so the programmer recognizes a concat, but also > adds something so the programmer recognizes: not strict. What about ||+ ? > > And then, at the conversion of e.g. (string || string || string) IS NULL, > confusion arises, but this is not due to the concat, but more to the IS NULL > clause together with the strange '' = null, and that must be handled > otherwise. > > So the operator would speed up part of the code conversion. > I have a different opinion. You have to change a application source code. So I don't like it in orafce. Maybe we can implement varchar2 text type and for this type redefine basic functions. But it could be a messy and maybe contraproductive. Orafce is tool for better migration, but it isn't full compatibility tool - what can be implemented effective and well, then can be in orafce. EnterpriseDB do "full" compatibility with Oracle but - I don't see a problem. Everybody who like operator ||| can do it very simple - not all have to be in Orafce. Regards Pavel Stehule > regards, > Yeb Havinga > > > >