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
