I have a horrible feeling that I'm missing the blindingly obvious here. But I
can't spot it. Help!
This simple setup produces the expected result:
create type s.t1 as (c1 text, c2 text);
select ('cat', 'dog')::s.t1;
This is the result:
(cat,dog)
create type s.t2 as (c1 int, c2 int);
select (17, 42)::s.t2;
This is the result:
(17,42)
(I know that plsql is doing an under-the-covers typecast to "text" to display
the result. The error (or at least, to me, shock) comes when I bring PL/pgSQL
into the picture:
create function s.f()
returns table(z text)
security definer
set search_path = pg_catalog, pg_temp
language plpgsql
as $body$
declare
r1 s.t1;
r2 s.t2;
txt text;
begin
r1 := (select ('cat', 'dog')::s.t1);
z := '1: '||r1.c1||' / '||r1.c2; return
next;
select ('cat', 'dog')::s.t1 into r1;
z := '2: '||r1.c1||' / '||coalesce(r1.c2, '<NULL>'); return
next;
r2 := (select (17, 42)::s.t2);
z := '3: '||(r2.c1)::text||' / '||(r2.c2)::text; return
next;
begin
select (17, 42)::s.t2 into r2;
exception
-- invalid input syntax for type integer: "(17,42)"
when invalid_text_representation then
z := 'invalid_text_representation caught'; return
next;
end;
select (17, 42)::s.t2 into txt;
r2 := txt;
z := '4: '||(r2.c1)::text||' / '||(r2.c2)::text; return
next;
end;
$body$;
select s.f();
txttxt
It produces this output:
1: cat / dog
2: (cat,dog) / <NULL>
3: 17 / 42
invalid_text_representation caught
4: 17 / 42
Results #1 and #3, from "UDT-value := (scaler subquery)", are what I expected.
Result #2 tells me what seems to be going on—and it dumbfounds me. The first
text field of my UDT value got "(cat,dog)"; and now that all input values have
been consumed, "c2" got NULL.
Do you (all) expect this? And if so, what's the story?
This outcome seems to explain the error. The text value "(17,42)" for "c1",
spirited up from "(17, 42)::s.t2", can't be converted to an integer.
Yet more mysterious is why the workaround, go via an intermediate text value,
succeeds:
select (17, 42)::s.t2 into txt;
r2 := txt;
But if I compress it thus:
select (((17, 42)::s.t2)::text)::s.t2 into r2;
then I'm back to the same 22P02 error:
invalid input syntax for type integer: "(17,42)"