Re: [pgsql-advocacy] Avoiding upgrade backlash

I have added additional documentation for this item to the release
notes, with examples:

Non-character values are no longer automatically cast to TEXT (Peter,
Tom)

Previously, a function or operator that took a TEXT parameter used to
automatically cast a non-TEXT value to TEXT and call the function or
operator (assuming there was no other matching function or operator).
That no longer happens and an explicit cast to TEXT is now required. For
example, these expressions now throw an error:

substr(current_date, 1, 1);
23 LIKE '%2%'
5.4 ~ '6';

but these work because of the explicit casts:

substr(current_date::text, 1, 1);
23::text LIKE '%2%
5.4::text ~ '6';

CHAR and VARCHAR still cast to TEXT automatically. Concatenation (||)
with non-TEXT types is still automatically cast, assuming one of the
parameters is textual. While this change will require additional casts
for some queries, it also eliminates some unusual behavior.

Hope it isn't too detailed.

---------------------------------------------------------------------------

Tom Lane wrote:
> Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
> > Am Montag, 12. November 2007 schrieb Josh Berkus:
> >> 3) If Robert gets his type-cast backport package together, the location of
> >> that.
>
> > Well, if you want to undo the changes, you don't need a backport
> > package; you can just change the cast's definition.
>
> It's actually not going to be that easy, because most of those casts
> aren't even in pg_cast anymore: they have been subsumed into the
> CoerceViaIO mechanism. You'd need to resurrect the individual cast
> functions before you could put entries back, too.
>
> Another little problem is that you're likely to break as much stuff as
> you fix. An example in CVS HEAD:
>
> regression=# select 42 || 'foo';
> ?column?
> ----------
> 42foo
> (1 row)
>
> regression=# select 42 like 'foo';
> ERROR: operator does not exist: integer ~~ unknown
> LINE 1: select 42 like 'foo';
> ^
> HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.
>
> OK, let's "fix" that by making int->text implicit again:
>
> regression=# create function inttotext(int) returns text as $$
> regression$# begin return $1; end$$ language plpgsql strict immutable;
> CREATE FUNCTION
> regression=# create cast (int as text) with function inttotext(int)
> regression-# as implicit;
> CREATE CAST
>
> Now LIKE works:
>
> regression=# select 42 like 'foo';
> ?column?
> ----------
> f
> (1 row)
>
> but || not so much:
>
> regression=# select 42 || 'foo';
> ERROR: operator is not unique: integer || unknown
> LINE 1: select 42 || 'foo';
> ^
> HINT: Could not choose a best candidate operator. You might need to add explicit type casts.
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://postgres.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +