Cast for text->Integer missing in 8.3.5

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

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

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

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.

"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

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?

"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