Downgrade implicit casts to text to be assignment-only, except for the ones

from the other string-category types; this eliminates a lot of surprising
interpretations that the parser could formerly make when there was no directly
applicable operator.

Create a general mechanism that supports casts to and from the standard string
types (text,varchar,bpchar) for *every* datatype, by invoking the datatype's
I/O functions.  These new casts are assignment-only in the to-string direction,
explicit-only in the other, and therefore should create no surprising behavior.
Remove a bunch of thereby-obsoleted datatype-specific casting functions.

The "general mechanism" is a new expression node type CoerceViaIO that can
actually convert between *any* two datatypes if their external text
representations are compatible.  This is more general than needed for the
immediate feature, but might be useful in plpgsql or other places in future.

This commit does nothing about the issue that applying the concatenation
operator || to non-text types will now fail, often with strange error messages
due to misinterpreting the operator as array concatenation.  Since it often
(not always) worked before, we should either make it succeed or at least give
a more user-friendly error; but details are still under debate.

Peter Eisentraut and Tom Lane

6 files changed, 12 insertions, 11 deletions

diff --git a/src/test/regress/expected/foreign_key.out b/src/test/regress/expected/foreign_key.out
index 41c2f397882..3c0dd7f0872 100644
--- a/src/test/regress/expected/foreign_key.out
+++ b/src/test/regress/expected/foreign_key.out
@@ -1125,10 +1125,12 @@ ALTER TABLE fktable ADD CONSTRAINT fk_3_1
 FOREIGN KEY (x3) REFERENCES pktable(id1);
 ERROR:  foreign key constraint "fk_3_1" cannot be implemented
 DETAIL:  Key columns "x3" and "id1" are of incompatible types: real and integer.
--- should succeed
--- int4 promotes to text, so this is allowed (though pretty durn debatable)
+-- int4 does not promote to text
 ALTER TABLE fktable ADD CONSTRAINT fk_1_2
 FOREIGN KEY (x1) REFERENCES pktable(id2);
+ERROR:  foreign key constraint "fk_1_2" cannot be implemented
+DETAIL:  Key columns "x1" and "id2" are of incompatible types: integer and character varying.
+-- should succeed
 -- int4 promotes to real
 ALTER TABLE fktable ADD CONSTRAINT fk_1_3
 FOREIGN KEY (x1) REFERENCES pktable(id3);
@@ -1150,7 +1152,7 @@ FOREIGN KEY (x2,x5,x3) REFERENCES pktable(id2,id1,id3);
 ALTER TABLE fktable ADD CONSTRAINT fk_123_231
 FOREIGN KEY (x1,x2,x3) REFERENCES pktable(id2,id3,id1);
 ERROR:  foreign key constraint "fk_123_231" cannot be implemented
-DETAIL:  Key columns "x2" and "id3" are of incompatible types: character varying and real.
+DETAIL:  Key columns "x1" and "id2" are of incompatible types: integer and character varying.
 ALTER TABLE fktable ADD CONSTRAINT fk_241_132
 FOREIGN KEY (x2,x4,x1) REFERENCES pktable(id1,id3,id2);
 ERROR:  foreign key constraint "fk_241_132" cannot be implemented
@@ -1162,7 +1164,6 @@ NOTICE:  drop cascades to constraint fk_123_123 on table fktable
 NOTICE:  drop cascades to constraint fk_5_1 on table fktable
 NOTICE:  drop cascades to constraint fktable_x1_fkey on table fktable
 NOTICE:  drop cascades to constraint fk_4_2 on table fktable
-NOTICE:  drop cascades to constraint fk_1_2 on table fktable
 NOTICE:  drop cascades to constraint fktable_x2_fkey on table fktable
 NOTICE:  drop cascades to constraint fk_1_3 on table fktable
 NOTICE:  drop cascades to constraint fktable_x3_fkey on table fktable
diff --git a/src/test/regress/expected/strings.out b/src/test/regress/expected/strings.out
index bd82846b1a4..31ab2bb5463 100644
--- a/src/test/regress/expected/strings.out
+++ b/src/test/regress/expected/strings.out
@@ -450,7 +450,7 @@ SELECT POSITION('4' IN '1234567890') = '4' AS "4";
  t
 (1 row)
 
-SELECT POSITION(5 IN '1234567890') = '5' AS "5";
+SELECT POSITION('5' IN '1234567890') = '5' AS "5";
  5 
 ---
  t
diff --git a/src/test/regress/expected/subselect.out b/src/test/regress/expected/subselect.out
index f36b5acfe86..5f50ba6e00d 100644
--- a/src/test/regress/expected/subselect.out
+++ b/src/test/regress/expected/subselect.out
@@ -354,7 +354,7 @@ create rule shipped_view_insert as on insert to shipped_view do instead
     insert into shipped values('wt', new.ordnum, new.partnum, new.value);
 insert into parts (partnum, cost) values (1, 1234.56);
 insert into shipped_view (ordnum, partnum, value)
-    values (0, 1, (select cost from parts where partnum = 1));
+    values (0, 1, (select cost from parts where partnum = '1'));
 select * from shipped_view;
  ttype | ordnum | partnum |  value  
 -------+--------+---------+---------
diff --git a/src/test/regress/sql/foreign_key.sql b/src/test/regress/sql/foreign_key.sql
index 16eee1e7545..5a0140c280e 100644
--- a/src/test/regress/sql/foreign_key.sql
+++ b/src/test/regress/sql/foreign_key.sql
@@ -760,12 +760,12 @@ FOREIGN KEY (x2) REFERENCES pktable(id1);
 ALTER TABLE fktable ADD CONSTRAINT fk_3_1
 FOREIGN KEY (x3) REFERENCES pktable(id1);
 
--- should succeed
-
--- int4 promotes to text, so this is allowed (though pretty durn debatable)
+-- int4 does not promote to text
 ALTER TABLE fktable ADD CONSTRAINT fk_1_2
 FOREIGN KEY (x1) REFERENCES pktable(id2);
 
+-- should succeed
+
 -- int4 promotes to real
 ALTER TABLE fktable ADD CONSTRAINT fk_1_3
 FOREIGN KEY (x1) REFERENCES pktable(id3);
diff --git a/src/test/regress/sql/strings.sql b/src/test/regress/sql/strings.sql
index 800028e4fcb..43b35ab667f 100644
--- a/src/test/regress/sql/strings.sql
+++ b/src/test/regress/sql/strings.sql
@@ -142,7 +142,7 @@ SELECT regexp_split_to_array('thE QUick bROWn FOx jUMPed ovEr THE lazy dOG', 'e'
 -- E021-11 position expression
 SELECT POSITION('4' IN '1234567890') = '4' AS "4";
 
-SELECT POSITION(5 IN '1234567890') = '5' AS "5";
+SELECT POSITION('5' IN '1234567890') = '5' AS "5";
 
 -- T312 character overlay function
 SELECT OVERLAY('abcdef' PLACING '45' FROM 4) AS "abc45f";
diff --git a/src/test/regress/sql/subselect.sql b/src/test/regress/sql/subselect.sql
index b8cb45c6fc7..3c501f1d1ba 100644
--- a/src/test/regress/sql/subselect.sql
+++ b/src/test/regress/sql/subselect.sql
@@ -218,7 +218,7 @@ create rule shipped_view_insert as on insert to shipped_view do instead
 insert into parts (partnum, cost) values (1, 1234.56);
 
 insert into shipped_view (ordnum, partnum, value)
-    values (0, 1, (select cost from parts where partnum = 1));
+    values (0, 1, (select cost from parts where partnum = '1'));
 
 select * from shipped_view;