[ sorry about having let this thread fall off my radar ]
jian he <[email protected]> writes:
> if we print out NameStr(att->attname) then error becomes:
> +DETAIL: Returned type unknown does not match expected type character
> varying in column "f2" (position 2).
> In this case, printing out {column \"%s\"} is not helpful at all.
Actually, the problem is that we should be printing the expected
column name not the input column name. At least in the test cases
we have, that gives a user-supplied name in every case. Even if
there are some cases where you just get "f2", that's not horrible.
So I don't think this is worth the amount of code churn involved in
the v2 patch --- let's just print it unconditionally, as attached.
I do still think that including the column number is potentially
helpful, though, so I didn't remove that.
regards, tom lane
diff --git a/src/backend/access/common/attmap.c b/src/backend/access/common/attmap.c
index 4b6cfe05c02..365243aeaad 100644
--- a/src/backend/access/common/attmap.c
+++ b/src/backend/access/common/attmap.c
@@ -96,33 +96,35 @@ build_attrmap_by_position(TupleDesc indesc,
same = true;
for (i = 0; i < n; i++)
{
- Form_pg_attribute att = TupleDescAttr(outdesc, i);
+ Form_pg_attribute outatt = TupleDescAttr(outdesc, i);
Oid atttypid;
int32 atttypmod;
- if (att->attisdropped)
+ if (outatt->attisdropped)
continue; /* attrMap->attnums[i] is already 0 */
noutcols++;
- atttypid = att->atttypid;
- atttypmod = att->atttypmod;
+ atttypid = outatt->atttypid;
+ atttypmod = outatt->atttypmod;
for (; j < indesc->natts; j++)
{
- att = TupleDescAttr(indesc, j);
- if (att->attisdropped)
+ Form_pg_attribute inatt = TupleDescAttr(indesc, j);
+
+ if (inatt->attisdropped)
continue;
nincols++;
/* Found matching column, now check type */
- if (atttypid != att->atttypid ||
- (atttypmod != att->atttypmod && atttypmod >= 0))
+ if (atttypid != inatt->atttypid ||
+ (atttypmod != inatt->atttypmod && atttypmod >= 0))
ereport(ERROR,
(errcode(ERRCODE_DATATYPE_MISMATCH),
errmsg_internal("%s", _(msg)),
- errdetail("Returned type %s does not match expected type %s in column %d.",
- format_type_with_typemod(att->atttypid,
- att->atttypmod),
+ errdetail("Returned type %s does not match expected type %s in column \"%s\" (position %d).",
+ format_type_with_typemod(inatt->atttypid,
+ inatt->atttypmod),
format_type_with_typemod(atttypid,
atttypmod),
+ NameStr(outatt->attname),
noutcols)));
attrMap->attnums[i] = (AttrNumber) (j + 1);
j++;
diff --git a/src/pl/plpgsql/src/expected/plpgsql_record.out b/src/pl/plpgsql/src/expected/plpgsql_record.out
index 6974c8f4a44..e5de7143606 100644
--- a/src/pl/plpgsql/src/expected/plpgsql_record.out
+++ b/src/pl/plpgsql/src/expected/plpgsql_record.out
@@ -28,7 +28,7 @@ create or replace function retc(int) returns two_int8s language plpgsql as
$$ begin return row($1,1); end $$;
select retc(42);
ERROR: returned record type does not match expected record type
-DETAIL: Returned type integer does not match expected type bigint in column 1.
+DETAIL: Returned type integer does not match expected type bigint in column "q1" (position 1).
CONTEXT: PL/pgSQL function retc(integer) while casting return value to function's return type
-- nor extra columns
create or replace function retc(int) returns two_int8s language plpgsql as
@@ -50,7 +50,7 @@ create or replace function retc(int) returns two_int8s language plpgsql as
$$ declare r record; begin r := row($1,1); return r; end $$;
select retc(42);
ERROR: returned record type does not match expected record type
-DETAIL: Returned type integer does not match expected type bigint in column 1.
+DETAIL: Returned type integer does not match expected type bigint in column "q1" (position 1).
CONTEXT: PL/pgSQL function retc(integer) while casting return value to function's return type
create or replace function retc(int) returns two_int8s language plpgsql as
$$ declare r record; begin r := row($1::int8, 1::int8, 42); return r; end $$;
@@ -386,7 +386,7 @@ DETAIL: Number of returned columns (2) does not match expected column count (3)
CONTEXT: PL/pgSQL function returnsrecord(integer) while casting return value to function's return type
select * from returnsrecord(42) as r(x int, y bigint); -- fail
ERROR: returned record type does not match expected record type
-DETAIL: Returned type integer does not match expected type bigint in column 2.
+DETAIL: Returned type integer does not match expected type bigint in column "y" (position 2).
CONTEXT: PL/pgSQL function returnsrecord(integer) while casting return value to function's return type
-- same with an intermediate record variable
create or replace function returnsrecord(int) returns record language plpgsql as
@@ -409,7 +409,7 @@ DETAIL: Number of returned columns (2) does not match expected column count (3)
CONTEXT: PL/pgSQL function returnsrecord(integer) while casting return value to function's return type
select * from returnsrecord(42) as r(x int, y bigint); -- fail
ERROR: returned record type does not match expected record type
-DETAIL: Returned type integer does not match expected type bigint in column 2.
+DETAIL: Returned type integer does not match expected type bigint in column "y" (position 2).
CONTEXT: PL/pgSQL function returnsrecord(integer) while casting return value to function's return type
-- should work the same with a missing column in the actual result value
create table has_hole(f1 int, f2 int, f3 int);
@@ -434,7 +434,7 @@ DETAIL: Number of returned columns (2) does not match expected column count (3)
CONTEXT: PL/pgSQL function returnsrecord(integer) while casting return value to function's return type
select * from returnsrecord(42) as r(x int, y bigint); -- fail
ERROR: returned record type does not match expected record type
-DETAIL: Returned type integer does not match expected type bigint in column 2.
+DETAIL: Returned type integer does not match expected type bigint in column "y" (position 2).
CONTEXT: PL/pgSQL function returnsrecord(integer) while casting return value to function's return type
-- same with an intermediate record variable
create or replace function returnsrecord(int) returns record language plpgsql as
@@ -457,7 +457,7 @@ DETAIL: Number of returned columns (2) does not match expected column count (3)
CONTEXT: PL/pgSQL function returnsrecord(integer) while casting return value to function's return type
select * from returnsrecord(42) as r(x int, y bigint); -- fail
ERROR: returned record type does not match expected record type
-DETAIL: Returned type integer does not match expected type bigint in column 2.
+DETAIL: Returned type integer does not match expected type bigint in column "y" (position 2).
CONTEXT: PL/pgSQL function returnsrecord(integer) while casting return value to function's return type
-- check access to a field of an argument declared "record"
create function getf1(x record) returns int language plpgsql as
@@ -545,6 +545,7 @@ begin
return next h;
return next row(5,6);
return next row(7,8)::has_hole;
+ return query select 9, 10;
end$$;
select returnssetofholes();
returnssetofholes
@@ -554,7 +555,8 @@ select returnssetofholes();
(3,4)
(5,6)
(7,8)
-(5 rows)
+ (9,10)
+(6 rows)
create or replace function returnssetofholes() returns setof has_hole language plpgsql as
$$
@@ -575,6 +577,16 @@ select returnssetofholes();
ERROR: returned record type does not match expected record type
DETAIL: Number of returned columns (3) does not match expected column count (2).
CONTEXT: PL/pgSQL function returnssetofholes() line 3 at RETURN NEXT
+create or replace function returnssetofholes() returns setof has_hole language plpgsql as
+$$
+begin
+ return query select 1, 2.0; -- fails
+end$$;
+select returnssetofholes();
+ERROR: structure of query does not match function result type
+DETAIL: Returned type numeric does not match expected type integer in column "f3" (position 2).
+CONTEXT: SQL statement "select 1, 2.0"
+PL/pgSQL function returnssetofholes() line 3 at RETURN QUERY
-- check behavior with changes of a named rowtype
create table mutable(f1 int, f2 text);
create function sillyaddone(int) returns int language plpgsql as
diff --git a/src/pl/plpgsql/src/sql/plpgsql_record.sql b/src/pl/plpgsql/src/sql/plpgsql_record.sql
index 96dcc414e92..4fbed38b8bb 100644
--- a/src/pl/plpgsql/src/sql/plpgsql_record.sql
+++ b/src/pl/plpgsql/src/sql/plpgsql_record.sql
@@ -338,6 +338,7 @@ begin
return next h;
return next row(5,6);
return next row(7,8)::has_hole;
+ return query select 9, 10;
end$$;
select returnssetofholes();
@@ -356,6 +357,13 @@ begin
end$$;
select returnssetofholes();
+create or replace function returnssetofholes() returns setof has_hole language plpgsql as
+$$
+begin
+ return query select 1, 2.0; -- fails
+end$$;
+select returnssetofholes();
+
-- check behavior with changes of a named rowtype
create table mutable(f1 int, f2 text);
diff --git a/src/test/regress/expected/plpgsql.out b/src/test/regress/expected/plpgsql.out
index c5f73fef297..d8ce39dba3c 100644
--- a/src/test/regress/expected/plpgsql.out
+++ b/src/test/regress/expected/plpgsql.out
@@ -3779,7 +3779,7 @@ end;
$$ language plpgsql;
select compos();
ERROR: returned record type does not match expected record type
-DETAIL: Returned type unknown does not match expected type character varying in column 2.
+DETAIL: Returned type unknown does not match expected type character varying in column "y" (position 2).
CONTEXT: PL/pgSQL function compos() while casting return value to function's return type
-- ... but this does
create or replace function compos() returns compostype as $$
