Postgres Pro
Downloads
Home   >   mailing lists

Thread:

From
Carlos Henrique Reimer
Date:
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.reimer@opendb.com.br

Re:

From
AI Rumman
Date:
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;

It'll work.


On Wed, May 15, 2013 at 3:17 PM, Carlos Henrique Reimer <carlos.reimer@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.reimer@opendb.com.br

Re:

From
Carlos Henrique Reimer
Date:
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@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;

It'll work.


On Wed, May 15, 2013 at 3:17 PM, Carlos Henrique Reimer <carlos.reimer@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.reimer@opendb.com.br




--
Reimer
47-3347-1724 47-9183-0547 msn: carlos.reimer@opendb.com.br

Re:

From
AI Rumman
Date:
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.reimer@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 <rummandba@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;

It'll work.


On Wed, May 15, 2013 at 3:17 PM, Carlos Henrique Reimer <carlos.reimer@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.reimer@opendb.com.br




--
Reimer
47-3347-1724 47-9183-0547 msn: carlos.reimer@opendb.com.br

Re:

From
Carlos Henrique Reimer
Date:
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@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.reimer@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 <rummandba@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;

It'll work.


On Wed, May 15, 2013 at 3:17 PM, Carlos Henrique Reimer <carlos.reimer@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.reimer@opendb.com.br




--
Reimer
47-3347-1724 47-9183-0547 msn: carlos.reimer@opendb.com.br




--
Reimer
47-3347-1724 47-9183-0547 msn: carlos.reimer@opendb.com.br

Re:

From
AI Rumman
Date:
Drop those functions and try again.


On Wed, May 15, 2013 at 4:22 PM, Carlos Henrique Reimer <carlos.reimer@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 <rummandba@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.reimer@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 <rummandba@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;

It'll work.


On Wed, May 15, 2013 at 3:17 PM, Carlos Henrique Reimer <carlos.reimer@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.reimer@opendb.com.br




--
Reimer
47-3347-1724 47-9183-0547 msn: carlos.reimer@opendb.com.br




--
Reimer
47-3347-1724 47-9183-0547 msn: carlos.reimer@opendb.com.br

Re:

From
Carlos Henrique Reimer
Date:
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@gmail.com> wrote:
Drop those functions and try again.


On Wed, May 15, 2013 at 4:22 PM, Carlos Henrique Reimer <carlos.reimer@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 <rummandba@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.reimer@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 <rummandba@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;

It'll work.


On Wed, May 15, 2013 at 3:17 PM, Carlos Henrique Reimer <carlos.reimer@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.reimer@opendb.com.br




--
Reimer
47-3347-1724 47-9183-0547 msn: carlos.reimer@opendb.com.br




--
Reimer
47-3347-1724 47-9183-0547 msn: carlos.reimer@opendb.com.br




--
Reimer
47-3347-1724 47-9183-0547 msn: carlos.reimer@opendb.com.br

Re:

From
AI Rumman
Date:
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.




On Wed, May 15, 2013 at 4:54 PM, Carlos Henrique Reimer <carlos.reimer@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 <rummandba@gmail.com> wrote:
Drop those functions and try again.


On Wed, May 15, 2013 at 4:22 PM, Carlos Henrique Reimer <carlos.reimer@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 <rummandba@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.reimer@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 <rummandba@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;

It'll work.


On Wed, May 15, 2013 at 3:17 PM, Carlos Henrique Reimer <carlos.reimer@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.reimer@opendb.com.br




--
Reimer
47-3347-1724 47-9183-0547 msn: carlos.reimer@opendb.com.br




--
Reimer
47-3347-1724 47-9183-0547 msn: carlos.reimer@opendb.com.br




--
Reimer
47-3347-1724 47-9183-0547 msn: carlos.reimer@opendb.com.br

Re:

From
Carlos Henrique Reimer
Date:
I got the same 144 rows here.

Have dropped the test database and recreated it again,  then performed these SQL commands:

select 'test'||1;

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;

select 'test'||1;

The first select worked but the second got the 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




On Wed, May 15, 2013 at 6:02 PM, AI Rumman <rummandba@gmail.com> wrote:
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.




On Wed, May 15, 2013 at 4:54 PM, Carlos Henrique Reimer <carlos.reimer@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 <rummandba@gmail.com> wrote:
Drop those functions and try again.


On Wed, May 15, 2013 at 4:22 PM, Carlos Henrique Reimer <carlos.reimer@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 <rummandba@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.reimer@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 <rummandba@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;

It'll work.


On Wed, May 15, 2013 at 3:17 PM, Carlos Henrique Reimer <carlos.reimer@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.reimer@opendb.com.br




--
Reimer
47-3347-1724 47-9183-0547 msn: carlos.reimer@opendb.com.br




--
Reimer
47-3347-1724 47-9183-0547 msn: carlos.reimer@opendb.com.br




--
Reimer
47-3347-1724 47-9183-0547 msn: carlos.reimer@opendb.com.br




--
Reimer
47-3347-1724 47-9183-0547 msn: carlos.reimer@opendb.com.br