Re: patch (for 9.1) string functions - Mailing list pgsql-hackers

Hello

2010/8/7 Itagaki Takahiro <itagaki.takahiro@gmail.com>:
> 2010/7/26 Robert Haas <robertmhaas@gmail.com>:
>> Come to think of it, have we checked that the behavior of LEFT, RIGHT,
>> REVERSE, etc. is the same on other DBs, especially as far as nulls,
>> empty strings, too-large or negative subscripts, etc is concerned?  Is
>> CONCAT('foo', NULL) => 'foo' really the behavior that everyone else
>> implements here?
>
> I made a discussion page in wiki for the compatibility issue.
> http://wiki.postgresql.org/wiki/String_Functions_and_Operators_Compatibility
>

nice, thank you

> Please fill empty cells and fix wrong descriptions.
>  * concat() is not compatible between MySQL and Oracle/DB2. Which do we buy?

I prefer a our implementation - it skip a NULL values and it has a
variadic arguments. MySQL's concat isn't too consistent - I don't know
why it has different NULL handlidg than concat_ws.

>  * How do other databases behave in left() and right() with negative lengths?

I don't know about one with left() and right() functions. What I know,
only MS Access has these functions. The design of these functions is
inspirited by wide used a Oracle library PLvision - this library is
freeware now - but my code is original. See plvstr.left() and
plvstr.right() - and little bit by python substring operations. The
sense of negative arguments is elimination of necessary detoast
operations and utf8 related calculations. For right() it means skip
first n chars, for left() skip last n chars. These functions was
originally designed for contrib - and I still thinking so contrib is
better - My opinion isn't strong here - I prefer a fully functional
function in contrib before minimalistic version in core. Minimalistic
functions are trivial via substring.

>  * Are there any databases that has similar features with format() or
> sprintf() ?

I know only about package from PLvision library -

select plvsubst.string('My name is %s %s', ARRAY['Pavel','Stěhule']);

but you can find a lot of custom implementations. I found a some
similar - not exactly this in T-SQL see FORMATMESSAGE() function. But
the using of this function is very limited and it is C API function
(available from T-SQL). It doesn't return a string, just write to log.

>
>
>> And why does CONCAT() take a variadic "ANY"
>> argument?  Shouldn't that be variadic TEXT?
>
> I think we have no other choice but to use VARIADIC "any" for variadic
> functions.
> We have all combinations of argument types for || operator, (text, text),
> (text, any), (any, text), but we cannot use such codes for variadic functions
> -- they have no limits of argument numbers. And in the case, the functions
> should be STABLE because they convert arguments to text in it with typout
> functions that might be STABLE.
>
>
> IMHO, I'd repeat, syntax for format() is a bad choice because it cannot
> concatenate multiple arguments without separator, though RAISE also uses it.
> %s format in sprintf() or {n} syntax in C#'s String.Format() seems to be
> a better design.

I don't agree. This function isn't designed to replace string
concation. It is designed to build a SQL string (for dynamic SQL) or
format messages. It isn't designed to replace to_char function. It is
designed to work mainly inside PLpgSQL functions and then is
consistent with RAISE statement.

Thank you

Regards

Pavel Stehule

>
> --
> Itagaki Takahiro
>