concatenation of strings

Hi,

I have a screwed problem with a simple string concatenation: If I concat
a string with the result of a function, which is also a string, I get an
error. If I do the same with a builtin function it works, also the
return types are equal. Here is an example:


--- first we drop own or postgres' definition of right()
drop function right (text,integer);

--- defining the function right()
CREATE FUNCTION right(text, integer) RETURNS text AS '
  SELECT substring($1 from char_length($1) - $2 + 1);
' LANGUAGE SQL;

--- asking for the return types of right() and substr()
select proname,prorettype from pg_proc where proname='substr' or
proname='right';
select typname from pg_type where typelem=25;

--- TEST 1: trying substr()
SELECT 'haha'||substr('jojo', 2);
--- TEST 2: trying right() returns an ERROR, athough it's the same type
as substr()!
SELECT 'haha'||right('jojo', 2);
--- TEST 3: trying a right() with cast to text (!) works!
SELECT 'haha'||cast(right('jojo', 2) AS text);
--- TEST 4: trying a right() with changed order of the concatenation
also works!
SELECT right('jojo', 2) || 'haha';


As you can see, both substr() and the self defined right() return text
as a type. If you cast the self defined right() to text it also works.
And also if you change the order, which is especially curious.

Any ideas for that?

Sincerely
    Joachim von Thadden
--
Joachim von Thadden            Linux Information Systems AG
Linux is our Business. ____________________________________ www.Linux-AG.com __

Linux-Trainings bundesweit - Termine unter http://www.linux-ag.com/training