Re:

It works if I drop the functions but then the select trim(1) does not work;

On Wed, May 15, 2013 at 5:38 PM, AI Rumman <rummandba(at)gmail(dot)com> wrote:

> Drop those functions and try again.
>
>
> On Wed, May 15, 2013 at 4:22 PM, Carlos Henrique Reimer <
> carlos(dot)reimer(at)opendb(dot)com(dot)br> wrote:
>
>> The PG version I'm using is 9.2.4.
>>
>> It works but after recreating the implicit cast with the function:
>>
>>
>> CREATE FUNCTION pg_catalog.text(integer) RETURNS text STRICT IMMUTABLE
>> LANGUAGE SQL AS 'SELECT textin(int4out($1));';
>> CREATE CAST (integer AS text) WITH FUNCTION pg_catalog.text(integer) AS
>> IMPLICIT;
>>
>> it does not work anymore, even using the explicit cast:
>>
>> select 'teste'||1::int;
>>
>> ERROR: operator is not unique: unknown || integer
>> LINE 1: select 'teste'||1::int;
>>
>> ^
>> HINT: Could not choose a best candidate operator. You might need to add
>> explicit type casts.
>>
>>
>> ********** Error **********
>>
>> ERROR: operator is not unique: unknown || integer
>> SQL state: 42725
>> Hint: Could not choose a best candidate operator. You might need to add
>> explicit type casts.
>> Character: 15
>>
>>
>>
>>
>>
>> On Wed, May 15, 2013 at 4:45 PM, AI Rumman <rummandba(at)gmail(dot)com> wrote:
>>
>>> Ah! I missed that.
>>> Which version of 9.2 you are working with? I am also at 9.2 and its
>>> working fine.
>>> Try out using
>>> select 'teste'||1::int;
>>>
>>> See if it works or not.
>>>
>>>
>>> On Wed, May 15, 2013 at 3:41 PM, Carlos Henrique Reimer <
>>> carlos(dot)reimer(at)opendb(dot)com(dot)br> wrote:
>>>
>>>> Actually, as stated in my first note, this is what I've done to fix the
>>>> select 2 (select trim(1);) but after creating this function/cast the select
>>>> 1 (select 'teste'||1;) started not working.
>>>>
>>>>
>>>> On Wed, May 15, 2013 at 4:24 PM, AI Rumman <rummandba(at)gmail(dot)com> wrote:
>>>>
>>>>> Create the functions:
>>>>> CREATE FUNCTION pg_catalog.text(integer) RETURNS text STRICT IMMUTABLE
>>>>> LANGUAGE SQL AS 'SELECT textin(int4out($1));';
>>>>> CREATE CAST (integer AS text) WITH FUNCTION pg_catalog.text(integer)
>>>>> AS IMPLICIT;
>>>>>
>>>>> http://www.rummandba.com/2010/10/migrate-database-from-postgresql-81-to.html
>>>>>
>>>>> It'll work.
>>>>>
>>>>>
>>>>> On Wed, May 15, 2013 at 3:17 PM, Carlos Henrique Reimer <
>>>>> carlos(dot)reimer(at)opendb(dot)com(dot)br> wrote:
>>>>>
>>>>>> Hi,
>>>>>>
>>>>>> Currently, our application is still using PG 8.2 and we are trying to
>>>>>> use 9.2 but there are some problems related with the implicit casts removed
>>>>>> on 8.3.
>>>>>>
>>>>>> Example:
>>>>>>
>>>>>> 1) select 'teste'||1;
>>>>>> 2) select trim(1);
>>>>>>
>>>>>> Select 1 & 2 does run fine on 8.2 but in 9.2 select 1 is ok and
>>>>>> select 2 got an error due the fact implicit cast were removed on PG 8.3:
>>>>>>
>>>>>> ERROR: function pg_catalog.btrim(integer) does not exist
>>>>>> LINE 1: select trim(1);
>>>>>> ^
>>>>>> HINT: No function matches the given name and argument types. You
>>>>>> might need to add explicit type casts.
>>>>>>
>>>>>> ********** Error **********
>>>>>>
>>>>>> ERROR: function pg_catalog.btrim(integer) does not exist
>>>>>> SQL state: 42883
>>>>>> Hint: No function matches the given name and argument types. You
>>>>>> might need to add explicit type casts.
>>>>>> Character: 8
>>>>>>
>>>>>> Recreated the implicit cast for select 2:
>>>>>>
>>>>>> CREATE FUNCTION pg_catalog.text(integer) RETURNS text STRICT
>>>>>> IMMUTABLE LANGUAGE SQL AS 'SELECT textin(int4out($1));';
>>>>>> CREATE CAST (integer AS text) WITH FUNCTION pg_catalog.text(integer)
>>>>>> AS IMPLICIT;
>>>>>>
>>>>>> Now select 2 works fine but select 1 gets an error:
>>>>>>
>>>>>> ERROR: operator is not unique: unknown || integer
>>>>>> LINE 1: select 'teste'||1;
>>>>>> ^
>>>>>> HINT: Could not choose a best candidate operator. You might need to
>>>>>> add explicit type casts.
>>>>>>
>>>>>> ********** Error **********
>>>>>>
>>>>>> ERROR: operator is not unique: unknown || integer
>>>>>> SQL state: 42725
>>>>>> Hint: Could not choose a best candidate operator. You might need to
>>>>>> add explicit type casts.
>>>>>> Character: 15
>>>>>>
>>>>>>
>>>>>> Is there a way to make both selects work on PG 9.2 without explicit
>>>>>> casts?
>>>>>>
>>>>>> Thank you in advace!
>>>>>>
>>>>>> --
>>>>>> Reimer
>>>>>> 47-3347-1724 47-9183-0547 msn: carlos(dot)reimer(at)opendb(dot)com(dot)br
>>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>> --
>>>> Reimer
>>>> 47-3347-1724 47-9183-0547 msn: carlos(dot)reimer(at)opendb(dot)com(dot)br
>>>>
>>>
>>>
>>
>>
>> --
>> Reimer
>> 47-3347-1724 47-9183-0547 msn: carlos(dot)reimer(at)opendb(dot)com(dot)br
>>
>
>

--
Reimer
47-3347-1724 47-9183-0547 msn: carlos(dot)reimer(at)opendb(dot)com(dot)br