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

[Bryn Llewellyn]

> t...@sss.pgh.pa.us wrote:
> 
>> b...@yugabyte.com writes:
>>    select (17, 42)::s.t2 into r2;
>> [ doesn't work ]
> 
> This would work as
> 
>   select 17, 42 into r2;
> 
> In general, SELECT INTO with a composite target expects to see a source 
> column per target field. If you want to assign a
> composite value to the whole target, don't use SELECT INTO; use an assignment 
> statement.

Thanks for the clue. Your "select 17, 42 into r2" does indeed work. But I can't 
find a way to act on your "If you want to assign a
composite value to the whole target… use an assignment statement".

Here's my test. It's closer to what I wanted to do. I wanted to assign a value 
to a PL/pgSQL variable whose data type is a composite type from component 
values from a table. And I happened to start off with "select into" rather than 
with a subquery on the RHS of an assignment. In another context, I want to 
construct a value of my composite type from variables that represent its 
components.

create table s.t(k int primary key, c1 int, c2 int);
insert into s.t(k, c1, c2) values(1, 17, 42);
create type s.x as (c1 int,  c2 int);

create function s.f()
  returns table(z text)
  security definer
  set search_path = pg_catalog, pg_temp
  language plpgsql
as $body$
declare
  my_c1 int := 17;
  my_c2 int := 42;
  r s.x;
  txt text;
begin
  r := (select (a.c1, a.c2)::s.x from s.t a where a.k = 1);
  z := (r.c1)::text||' / '||(r.c2)::text;                             return 
next;

  select a.c1, a.c2 into r from s.t a where a.k = 1;
  z := (r.c1)::text||' / '||(r.c2)::text;                             return 
next;

  begin
    r := (my_c1, my_c2)::s.t;
  exception when cannot_coerce then
    z := '"cannot_coerce" handled.';                                  return 
next;
  end;

  r.c1 := my_c1;
  r.c2 := my_c2;
  z := (r.c1)::text||' / '||(r.c2)::text;                             return 
next;
end;
$body$;

select s.f();

This is specific to "language plpgsql" subprograms. So I should find the rules 
that I need to understand in "Chapter 43. PL/pgSQL - SQL Procedural Language" 
(www.postgresql.org/docs/11/plpgsql.html). But I can't. However, I'm not very 
good at finding the relevant doc when I need it. Where is it?

Going from what you said, and my most recent test, here, the rules seem to be 
inscrutable—and non-composable.

I wonder if it all boils down to the strange-to-me anonymous, polymorphic 
"record" notion.