Thread: 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

Here are the results of the tests below, so that you need not repeat
them. I use the newest version (7.2.2) of Postgres.

select proname,prorettype from pg_proc where proname='substr' or proname='right';

 proname | prorettype
---------+------------
 substr  |         25
 substr  |         25
 right   |         25
(3 Zeilen)

select typname from pg_type where typelem=25;

 typname
---------
 _text
(1 Zeile)

SELECT 'haha'||substr('jojo', 2);

 ?column?
----------
 hahaojo
(1 Zeile)

SELECT 'haha'||right('jojo', 2);

ERROR:  parser: parse error at or near "right"

SELECT 'haha'||cast(right('jojo', 2) AS text);

 ?column?
----------
 hahajo
(1 Zeile)

SELECT right('jojo', 2) || 'haha';

 ?column?
----------
 johaha
(1 Zeile)

Sincerely
    Joachim von Thadden

Am Die, Sep 10, 2002 at 12:37:39 +0000 schrieb Joachim von Thadden:
> 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:
--
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

On Tue, Sep 10, 2002 at 12:37:39PM +0000, Joachim von Thadden wrote:
> 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:

It would've helped if you'd actually included the output of the queries you
executed. It does actually work if you use a name other than "right", for
example, "right2". This would indicate a parser problem. I can't see it in
the source right now.

Anyone else know?


--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> There are 10 kinds of people in the world, those that can do binary
> arithmetic and those that can't.

I had a similar problem with LEFT function...
This is due to the fact that LEFT and RIGTH are reserved keywords.

Here is the answer I got :
----------------------------------------------------------------------
Use a different function name.  LEFT is a reserved word, and while PG
will let you get away with using it as a function name anyway, there
are situations like this where the normal interpretation of the keyword
takes precedence.

I've tweaked the grammar for 7.3 so that this particular case works,
but you'd still have similar problems if you were to use, say, BETWEEN
as a function name.

            regards, tom lane
----------------------------------------------------------------------

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org]On Behalf Of Joachim von
Thadden
Sent: Tuesday, September 10, 2002 2:44 PM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] concatenation of strings


Here are the results of the tests below, so that you need not repeat
them. I use the newest version (7.2.2) of Postgres.

select proname,prorettype from pg_proc where proname='substr' or
proname='right';

 proname | prorettype
---------+------------
 substr  |         25
 substr  |         25
 right   |         25
(3 Zeilen)

select typname from pg_type where typelem=25;

 typname
---------
 _text
(1 Zeile)

SELECT 'haha'||substr('jojo', 2);

 ?column?
----------
 hahaojo
(1 Zeile)

SELECT 'haha'||right('jojo', 2);

ERROR:  parser: parse error at or near "right"

SELECT 'haha'||cast(right('jojo', 2) AS text);

 ?column?
----------
 hahajo
(1 Zeile)

SELECT right('jojo', 2) || 'haha';

 ?column?
----------
 johaha
(1 Zeile)

Sincerely
    Joachim von Thadden

Am Die, Sep 10, 2002 at 12:37:39 +0000 schrieb Joachim von Thadden:
> 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:
--
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