Resp: RE: Resp: [SQL] Concatenation in selects

>
>> select ltrim(first_name) || (' ' || ltrim(last_name)) as full_name
>> from
>> ...
>>
>> where "ltrim" is used only to convert both, first_time and
>> last_time, to text. I wish I could convert among string types
>> (bpchar, varchar and text) without any tricks, but it seems this
>> is not implemented yet.
>>
>Ever tried the cast() function?
> -DEJ
>

Yes, but I haven´t been successful.

This is an example of what I have tried:

httpd=> create table t1 (f1 char(4), f2 varchar(4), f3 text);
CREATE
httpd=> \d t1

Table = t1
+----------------------------+----------------------------+-------+
| Field | Type | Length|
-----------------------------+----------------------------+-------+
| f1 | char() | 4 |
| f2 | varchar() | 4 |
| f3 | text | var |
+----------------------------+----------------------------+-------+

httpd=> insert into t1 values ('1234', '5678', '9012');
INSERT 648441 1

httpd=> select f1 || f1 from t1;
?column?
--------
12341234
(1 row)

OK, when both operands have the same type, but

httpd=> select f1::varchar || f2 from t1;
ERROR: Sep 3 16:53:00:function varchar(bpchar) does not exist
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

On the other hand, conversions to integer do work:

httpd=> select f1::int2 + f2::int2 from t1;
?column?
--------
6912
(1 row)

finally,

httpd=> select (f1 || f1) || f3 from t1;
ERROR: Sep 3 17:15:36:There is no operator '||' for types 'bpchar' and
'text'
You will either have to retype this query using an explicit cast,
or you will have to define the operator using CREATE OPERATOR

The result of (f1 || f1) is of type bpchar, but f3 is of type text.

httpd=> select (f2 || f2) || f3 from t1;
ERROR: Sep 3 17:16:20:There is no operator '||' for types 'varchar' and
'text'
You will either have to retype this query using an explicit cast,
or you will have to define the operator using CREATE OPERATOR

The result of (f2 || f2) is of type varchar, but f3 is of type text.

httpd=> select (f3 || f3) || f3 from t1;
?column?
------------
901290129012
(1 row)

OK, because all operands have the same type.

It´s interesting that, despite the documentation (\do) states that the
result of the operator "||" is of type text, it's allways resulting a
value with the same type as of its operands.

It´s worth noting that the function "textcat" works with any type of
operand.

Fidelis Assis
MCI-Embratel - Rio de Janeiro