Re: Add column name to error description

On Mon, Apr 1, 2024 at 3:15 AM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
>
> > The format "%d-%s" is not ideal. I suggesst "%d (%s)".
>
> I didn't like that either, for two reasons: if we have a column name
> it ought to be the prominent label, but we might not have one if the
> TupleDesc came from some anonymous source (possibly that case explains
> the test crash? Although I think the attname would be an empty string
> rather than missing entirely in such cases). I think it'd be worth
> providing two distinct message strings:
>
> "Returned type %s does not match expected type %s in column \"%s\" (position %d)."
> "Returned type %s does not match expected type %s in column position %d."
>
> I'd suggest dropping the column number entirely in the first case,
> were it not that the attnames might well not be unique if we're
> dealing with an anonymous record type such as a SELECT result.
>

please check the attached POC, hope the output is what you expected.
now we can output these two message.
> "Returned type %s does not match expected type %s in column \"%s\" (position %d)."
> "Returned type %s does not match expected type %s in column position %d."

create type compostype as (x int, y varchar);
create or replace function compos() returns compostype as $$
begin return (1, 'hello'); end;
$$ language plpgsql;

select compos();
HEAD error message is
ERROR: returned record type does not match expected record type
DETAIL: Returned type unknown does not match expected type character
varying in column 2.
CONTEXT: PL/pgSQL function compos() while casting return value to
function's return type

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.

---------------case1
create function my_f(a integer, b integer)
returns table(first_col integer, lots_of_cols_later numeric) language plpgsql as
$function$
begin
return query select a, b;
end;
$function$;

-----------------case2
create or replace function returnsrecord(int) returns record language plpgsql as
$$ begin return row($1,$1+1); end $$;

select * from my_f(1,1);
select * from returnsrecord(42) as r(x int, y bigint);

In the first case, we want to print out the column \"%s\",
but in the second case, we don't.

in plpgsql_exec_function
first case, return first tuple values then check tuple attributes
in the second case, check the tuple attribute error out immediately.

build_attrmap_by_position both indesc->tdtypeid = 2249, outdesc->tdtypeid = 2249
so build_attrmap_by_position itself cannot distinguish these two cases.

To solve this,
we can add a boolean argument to convert_tuples_by_position.

Also this error
ERROR: structure of query does not match function result type
occurred quite often on the internet, see [1]
but there are no tests for it.
so we can add a test in src/test/regress/sql/plpgsql.sql

[1] https://stackoverflow.com/search?q=structure+of+query+does+not+match+function+result+type