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;
(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)"
