select (17, 42)::s.t2 into... fails with "invalid input syntax"

Поиск
Список
Период
Сортировка
От Bryn Llewellyn
Тема select (17, 42)::s.t2 into... fails with "invalid input syntax"
Дата
Msg-id C68A39DC-A728-41A2-ADD7-A004DB4156FD@yugabyte.com
обсуждение исходный текст
Ответы Re: select (17, 42)::s.t2 into... fails with "invalid input syntax"  ("David G. Johnston" <david.g.johnston@gmail.com>)
Re: select (17, 42)::s.t2 into... fails with "invalid input syntax"  (Tom Lane <tgl@sss.pgh.pa.us>)
Список pgsql-general
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)"

В списке pgsql-general по дате отправления:

Предыдущее
От: Ron
Дата:
Сообщение: Re: Behavior of PL/pgSQL function following drop and re-create of a table that it uses
Следующее
От: "David G. Johnston"
Дата:
Сообщение: Re: select (17, 42)::s.t2 into... fails with "invalid input syntax"