I am using the same version and its fine.
postgres=# select version();
version
---------------------------------------------------------------------------------------------------------------
PostgreSQL 9.2.4 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.1.2
20080704 (Red Hat 4.1.2-52), 64-bit
Try out the query:
SELECT ct.*,
source_t.typname as source_type
,target_t.typname as target_type
, proc.proname
FROM
pg_cast as ct
, pg_type as source_t
, pg_type as target_t
,pg_proc as proc
WHERE
ct.castsource = source_t.oid
and ct.casttarget = target_t.oid
and ct.castfunc = proc.oid
I get 144 rows.
http://www.rummandba.com/2013/02/postgresql-type-casting-information.html
On Wed, May 15, 2013 at 4:54 PM, Carlos Henrique Reimer <
carlos.rei...@opendb.com.br> wrote:
> 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 <rumman...@gmail.com> wrote:
>
>> Drop those functions and try again.
>>
>>
>> On Wed, May 15, 2013 at 4:22 PM, Carlos Henrique Reimer <
>> carlos.rei...@opendb.com.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 <rumman...@gmail.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.rei...@opendb.com.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 <rumman...@gmail.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.rei...@opendb.com.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.rei...@opendb.com.br
>>>>>>>
>>>>>>
>>>>>>
>>>>>
>>>>>
>>>>> --
>>>>> Reimer
>>>>> 47-3347-1724 47-9183-0547 msn: carlos.rei...@opendb.com.br
>>>>>
>>>>
>>>>
>>>
>>>
>>> --
>>> Reimer
>>> 47-3347-1724 47-9183-0547 msn: carlos.rei...@opendb.com.br
>>>
>>
>>
>
>
> --
> Reimer
> 47-3347-1724 47-9183-0547 msn: carlos.rei...@opendb.com.br
>
