Postgres Pro
Downloads
Home   >   mailing lists

Thread: Cast for text->Integer missing in 8.3.5

Cast for text->Integer missing in 8.3.5

From
"Nykolyn, Andrew"
Date:

I am trying to upgrade my Postgres server from 8.2.3 to 8.3.5 and have found that the cast text->integer and integer->text are missing.  Is there a reason why they are not there and how can I get them back.  I have many stored procedures that rely on those casts

Re: Cast for text->Integer missing in 8.3.5

From
Emanuel Calvo Franco
Date:
2009/1/13 Nykolyn, Andrew <andrew.nykolyn@ngc.com>:
> I am trying to upgrade my Postgres server from 8.2.3 to 8.3.5 and have found
> that the cast text->integer and integer->text are missing.  Is there a
> reason why they are not there and how can I get them back.  I have many
> stored procedures that rely on those casts

http://code.open-bio.org/svnweb/index.cgi/biosql/revision?rev=284
here are the functions

--
      Emanuel Calvo Franco
   ArPUG / AOSUG Member
 Postgresql Support & Admin

Re: Cast for text->Integer missing in 8.3.5

From
Aleksander Kmetec
Date:
Nykolyn, Andrew wrote:
> I am trying to upgrade my Postgres server from 8.2.3 to 8.3.5 and have
> found that the cast text->integer and integer->text are missing.  Is
> there a reason why they are not there and how can I get them back.  I
> have many stored procedures that rely on those casts

I'm right in the middle of trying to solve the same problem, myself.
I started with this link: http://code.open-bio.org/svnweb/index.cgi/biosql/revision?rev=284
But that wasn't enough. In fact, it broke some things that worked before I added the missing implicit casts. Like this,

for example:
select 123::double precision || 'abc';
ERROR:  operator is not unique: double precision || unknown

So I also had to define "||(double precision, text)" and "||(text, double precision)" operators. I'm not sure how much
additional work might be needed, but here's what I have so far for adding backwards compatility for "double precision"
datatype:

CREATE FUNCTION pg_catalog.text(double precision)
     RETURNS text STRICT IMMUTABLE
     LANGUAGE SQL AS 'SELECT textin(float8out($1));';

CREATE CAST (double precision AS text)
     WITH FUNCTION pg_catalog.text(double precision) AS IMPLICIT;

CREATE FUNCTION pg_catalog.compat_textcat(double precision, text)
     RETURNS text STRICT IMMUTABLE
     LANGUAGE SQL AS 'SELECT textcat(CAST($1 AS TEXT), $2);';

CREATE FUNCTION pg_catalog.compat_textcat(text, double precision)
     RETURNS text STRICT IMMUTABLE
     LANGUAGE SQL AS 'SELECT textcat($1, CAST($2 AS TEXT));';

CREATE OPERATOR pg_catalog.|| (
     PROCEDURE = compat_textcat,
     LEFTARG = double precision,
     RIGHTARG = text
);

CREATE OPERATOR pg_catalog.|| (
     PROCEDURE = compat_textcat,
     LEFTARG = text,
     RIGHTARG = double precision
);


Regards,
Aleksander

Re: Cast for text->Integer missing in 8.3.5

From
Emanuel Calvo Franco
Date:
2009/1/13 Aleksander Kmetec <aleksander.kmetec@intera.si>:
>
> Nykolyn, Andrew wrote:
>>
>> I am trying to upgrade my Postgres server from 8.2.3 to 8.3.5 and have
>> found that the cast text->integer and integer->text are missing.  Is there a
>> reason why they are not there and how can I get them back.  I have many
>> stored procedures that rely on those casts
>
> I'm right in the middle of trying to solve the same problem, myself.
> I started with this link:
> http://code.open-bio.org/svnweb/index.cgi/biosql/revision?rev=284
> But that wasn't enough. In fact, it broke some things that worked before I
> added the missing implicit casts. Like this, for example:
> select 123::double precision || 'abc';
> ERROR:  operator is not unique: double precision || unknown
>
> So I also had to define "||(double precision, text)" and "||(text, double
> precision)" operators. I'm not sure how much additional work might be
> needed, but here's what I have so far for adding backwards compatility for
> "double precision" datatype:
>
> CREATE FUNCTION pg_catalog.text(double precision)
>    RETURNS text STRICT IMMUTABLE
>    LANGUAGE SQL AS 'SELECT textin(float8out($1));';
>
> CREATE CAST (double precision AS text)
>    WITH FUNCTION pg_catalog.text(double precision) AS IMPLICIT;
>
> CREATE FUNCTION pg_catalog.compat_textcat(double precision, text)
>    RETURNS text STRICT IMMUTABLE
>    LANGUAGE SQL AS 'SELECT textcat(CAST($1 AS TEXT), $2);';
>
> CREATE FUNCTION pg_catalog.compat_textcat(text, double precision)
>    RETURNS text STRICT IMMUTABLE
>    LANGUAGE SQL AS 'SELECT textcat($1, CAST($2 AS TEXT));';
>
> CREATE OPERATOR pg_catalog.|| (
>    PROCEDURE = compat_textcat,
>    LEFTARG = double precision,
>    RIGHTARG = text
> );
>
> CREATE OPERATOR pg_catalog.|| (
>    PROCEDURE = compat_textcat,
>    LEFTARG = text,
>    RIGHTARG = double precision
> );
>
>
> Regards,
> Aleksander
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

I forgive to tell you that you must add operators...d'oh!

--
      Emanuel Calvo Franco
   ArPUG / AOSUG Member
 Postgresql Support & Admin

Re: Cast for text->Integer missing in 8.3.5

From
"Scott Marlowe"
Date:
On Tue, Jan 13, 2009 at 8:23 AM, Nykolyn, Andrew <andrew.nykolyn@ngc.com> wrote:
> I am trying to upgrade my Postgres server from 8.2.3 to 8.3.5 and have found
> that the cast text->integer and integer->text are missing.  Is there a
> reason why they are not there and how can I get them back.  I have many
> stored procedures that rely on those casts

They were removed because the output was unpredictable.  It's far
better to spend your time fixing your code than trying to reinstate
buggy / questionable behaviour.

Re: Cast for text->Integer missing in 8.3.5

From
Tom Lane
Date:
"Scott Marlowe" <scott.marlowe@gmail.com> writes:
> On Tue, Jan 13, 2009 at 8:23 AM, Nykolyn, Andrew <andrew.nykolyn@ngc.com> wrote:
>> I am trying to upgrade my Postgres server from 8.2.3 to 8.3.5 and have found
>> that the cast text->integer and integer->text are missing.  Is there a
>> reason why they are not there and how can I get them back.  I have many
>> stored procedures that rely on those casts

> They were removed because the output was unpredictable.  It's far
> better to spend your time fixing your code than trying to reinstate
> buggy / questionable behaviour.

To clarify: those casts are not *gone*, they just don't occur implicitly
anymore.  You should put explicit casts into your functions in those
places where you actually intend an integer to be converted to text or
vice versa.  Don't be surprised if this turns up some bugs in your code,
ie places where you didn't really intend the semantics you were getting.

            regards, tom lane

Re: Cast for text->Integer missing in 8.3.5

From
"Nykolyn, Andrew"
Date:
2009/1/13 Aleksander Kmetec <aleksander.kmetec@intera.si>:
>
> Nykolyn, Andrew wrote:
>>
>> I am trying to upgrade my Postgres server from 8.2.3 to 8.3.5 and
>> have found that the cast text->integer and integer->text are missing.

>> Is there a reason why they are not there and how can I get them back.

>> I have many stored procedures that rely on those casts
>
> I'm right in the middle of trying to solve the same problem, myself.
> I started with this link:
> http://code.open-bio.org/svnweb/index.cgi/biosql/revision?rev=284
> But that wasn't enough. In fact, it broke some things that worked
> before I added the missing implicit casts. Like this, for example:
> select 123::double precision || 'abc';
> ERROR:  operator is not unique: double precision || unknown
>
> So I also had to define "||(double precision, text)" and "||(text,
> double precision)" operators. I'm not sure how much additional work
> might be needed, but here's what I have so far for adding backwards
> compatility for "double precision" datatype:
>
> CREATE FUNCTION pg_catalog.text(double precision)
>    RETURNS text STRICT IMMUTABLE
>    LANGUAGE SQL AS 'SELECT textin(float8out($1));';
>
> CREATE CAST (double precision AS text)
>    WITH FUNCTION pg_catalog.text(double precision) AS IMPLICIT;
>
> CREATE FUNCTION pg_catalog.compat_textcat(double precision, text)
>    RETURNS text STRICT IMMUTABLE
>    LANGUAGE SQL AS 'SELECT textcat(CAST($1 AS TEXT), $2);';
>
> CREATE FUNCTION pg_catalog.compat_textcat(text, double precision)
>    RETURNS text STRICT IMMUTABLE
>    LANGUAGE SQL AS 'SELECT textcat($1, CAST($2 AS TEXT));';
>
> CREATE OPERATOR pg_catalog.|| (
>    PROCEDURE = compat_textcat,
>    LEFTARG = double precision,
>    RIGHTARG = text
> );
>
> CREATE OPERATOR pg_catalog.|| (
>    PROCEDURE = compat_textcat,
>    LEFTARG = text,
>    RIGHTARG = double precision
> );
>

The above has all worked great to get me past the two issues described
so far.  Now I am having a problem with: function quote_literal is not
unique.  Is there anything to make that backward compatible?

Re: Cast for text->Integer missing in 8.3.5

From
Tom Lane
Date:
"Nykolyn, Andrew" <andrew.nykolyn@ngc.com> writes:
> The above has all worked great to get me past the two issues described
> so far.  Now I am having a problem with: function quote_literal is not
> unique.

And you're going to have a few other problems after you get past that.
You can't just insert implicit casts and expect that it will affect only
the situations you want it to affect.  The reason we downgraded the
implicit casts to begin with was exactly that they kicked in too often.

You might try backing off to just having the special || operators
and not the implicit casts, and see if that gets you to where you want,
or at least close enough that fixing your remaining code properly is not
too big a task.  Extra operators are not nearly as dangerous as implicit
casts.

            regards, tom lane