Обсуждение: Autocast script of peter e in PostgreSQL 8.3

If i use this script
http://people.planetpostgresql.org/peter/index.php?/archives/18-Readding-implicit-casts-in-PostgreSQL-8.3.html
in PostgreSql 8.3 to reactivate autocast i get the following problems:


*without autocast* script (i learned that 8.3 does autocast if one of
the || parameters is Text(or Char/Varchar) :-P ):

demo=# SELECT 1||'~'||1;
 ?column?
----------
 1~1
(1 row)


*with autocast:

*SCDS=# SELECT 1||'~'||1;
ERROR:  operator is not unique: integer || unknown at character 9
HINT:  Could not choose a best candidate operator. You might need to add
explicit type casts.
LINE 1: SELECT 1||'~'||1;

SCDS=# SELECT 1||'~'::TEXT||1;
ERROR:  operator is not unique: integer || text at character 9
HINT:  Could not choose a best candidate operator. You might need to add
explicit type casts.
LINE 1: SELECT 1||'~'::TEXT||1;


Any hints?

Daniel.

On Tue, May 06, 2008 at 02:09:30PM +0200, Daniel Schuchardt wrote:
> *SCDS=# SELECT 1||'~'||1;
> ERROR:  operator is not unique: integer || unknown at character 9
> HINT:  Could not choose a best candidate operator. You might need to add
> explicit type casts.
> LINE 1: SELECT 1||'~'||1;

Check the archives. I think Tom pointed out that while it readds all
the casts, some other changes were made for the '||' operator and you
need to delete those. IIRC you need to manipuate the catalog tables
directly for that.

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Please line up in a tree and maintain the heap invariant while
> boarding. Thank you for flying nlogn airlines.

Martijn van Oosterhout schrieb:
> On Tue, May 06, 2008 at 02:09:30PM +0200, Daniel Schuchardt wrote:
>
>> *SCDS=# SELECT 1||'~'||1;
>> ERROR:  operator is not unique: integer || unknown at character 9
>> HINT:  Could not choose a best candidate operator. You might need to add
>> explicit type casts.
>> LINE 1: SELECT 1||'~'||1;
>>
>
> Check the archives. I think Tom pointed out that while it readds all
> the casts, some other changes were made for the '||' operator and you
> need to delete those. IIRC you need to manipuate the catalog tables
> directly for that.
>
> Have a nice day,
>

Hey Matijn,

it simply does not work.  Every created CAST will crash with the '||'
operator.

Thnx for your comments,

Daniel.



DEMO1=# SELECT 1||'~'||1;
 ?column?
----------
 1~1
(1 row)
DEMO1=# CREATE FUNCTION pg_catalog.text(integer) RETURNS text STRICT
IMMUTABLE LANGUAGE SQL AS 'SELECT textin(int4out($1));';
CREATE FUNCTION
DEMO1=# CREATE CAST (integer AS text) WITH FUNCTION
pg_catalog.text(integer) ASIMPLICIT;
CREATE CAST
DEMO1=# SELECT 1||'~'||1;
ERROR:  operator is not unique: integer || unknown at character 9
HINT:  Could not choose a best candidate operator. You might need to add
explicit type casts.
LINE 1: SELECT 1||'~'||1;

DEMO1=# SELECT 1.1||'~'||1.1;
 ?column?
----------
 1.1~1.1
(1 row)
DEMO1=# CREATE FUNCTION pg_catalog.text(numeric) RETURNS text STRICT
IMMUTABLE LANGUAGE SQL AS 'SELECT textin(numeric_out($1));';
CREATE FUNCTION
DEMO1=# CREATE CAST (numeric AS text) WITH FUNCTION
pg_catalog.text(numeric) AS IMPLICIT;
CREATE CAST
DEMO1=# SELECT 1.1||'~'||1.1;
ERROR:  operator is not unique: numeric || unknown at character 11
HINT:  Could not choose a best candidate operator. You might need to add
explicit type casts.
LINE 1: SELECT 1.1||'~'||1.1;

DEMO1=# SELECT current_date||'~'||current_date;
       ?column?
-----------------------
 2008-05-06~2008-05-06
(1 row)
DEMO1=# CREATE FUNCTION pg_catalog.text(date) RETURNS text STRICT
IMMUTABLE LANG
UAGE SQL AS 'SELECT textin(date_out($1));';
CREATE FUNCTION
DEMO1=# CREATE CAST (date AS text) WITH FUNCTION pg_catalog.text(date)
AS IMPLICIT;
CREATE CAST
DEMO1=# SELECT current_date||'~'||current_date;
ERROR:  operator is not unique: date || unknown at character 20
HINT:  Could not choose a best candidate operator. You might need to add
explicit type casts.
LINE 1: SELECT current_date||'~'||current_date;
                           ^

On Tue, May 06, 2008 at 03:37:57PM +0200, Daniel Schuchardt wrote:
> Hey Matijn,
>
> it simply does not work.  Every created CAST will crash with the '||'
> operator.
>
> Thnx for your comments,

Did you not find this during your searching?
http://archives.postgresql.org/pgsql-general/2008-03/msg00995.php
and followups.

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Please line up in a tree and maintain the heap invariant while
> boarding. Thank you for flying nlogn airlines.

Martijn van Oosterhout schrieb:
>
>
> Did you not find this during your searching?
> http://archives.postgresql.org/pgsql-general/2008-03/msg00995.php
> and followups.
>
> Have a nice day,
>

no i didnt found it in google.

now everything is working and as excpected:

EXPLAIN ANALYSE big query

Runtime 450 ms in Postgres 8.1
Runtime 199 ms in Postgres 8.3

thats fine :-)


thanks a lot.