Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pa6Ub-0003fm-By for pgsql-general@arkaria.postgresql.org; Thu, 09 Mar 2023 02:58:57 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pa6Ua-0001WF-3T for pgsql-general@arkaria.postgresql.org; Thu, 09 Mar 2023 02:58:56 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pa6UZ-0001W5-Lj for pgsql-general@lists.postgresql.org; Thu, 09 Mar 2023 02:58:55 +0000 Received: from mail-pl1-x633.google.com ([2607:f8b0:4864:20::633]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1pa6UW-00049r-4s for pgsql-general@lists.postgresql.org; Thu, 09 Mar 2023 02:58:55 +0000 Received: by mail-pl1-x633.google.com with SMTP id v11so574393plz.8 for ; Wed, 08 Mar 2023 18:58:51 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yugabyte.com; s=google; t=1678330730; h=references:to:cc:in-reply-to:date:subject:mime-version:message-id :from:from:to:cc:subject:date:message-id:reply-to; bh=RuHfmekOoNq5a3xZOcetsKvNQ4+jAxthS7A78ijwH0c=; b=FLfiqIaHpd+d4OBIgxJboAOAhJGAme4HCAKIETMApKo1sHJKBOkwZDIvamhKWu4YaB bPDSPrxMt10C0+BGXy8svI6ktqsvvEzbdwCijSx7ZnKd6Jvcxu8N3Z2NKfEcxp83to+9 ihK6wQ0S4xXn55sjaXGKz9X4TjQaejimkOC9U= X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; t=1678330730; h=references:to:cc:in-reply-to:date:subject:mime-version:message-id :from:x-gm-message-state:from:to:cc:subject:date:message-id:reply-to; bh=RuHfmekOoNq5a3xZOcetsKvNQ4+jAxthS7A78ijwH0c=; b=noT/JtiE6SSLkEVNsCfSqGFMvQHrQKV25MR6+qJsNPEsrYwlI6qBPpW/PQKnu4+HBT KWzAl2nZnv/NXbR7kKk9vDrkT39V1in0W/GHvIvZRoNpevVWnOt4pyw55nZjFmlC3ErM 7vZTx9m/y6Vm7KgXLu2MxVqgGMy4g8zSuwciEbJNKUpfRnVXA8va2R5oIqLxelYO2Ys7 P5jDsCbMGFD6sKFv4Uz4hfUB3RnbHrq5GyL2RLKeA8OwVXslUpCixkFXfi6oBFkNcg5x xq3iD13ENCuW/9frFvH0Umd94N8SVZkwSbbdZDUR0YH88CG3OY8oDgTmg25sfJ8nz4Hl S7zw== X-Gm-Message-State: AO0yUKX4ivU5cMCI0lubIk/i89NbH/zvH6sVo+y0p4Aby5b89Cno15qe CnC73QlyXFYJOWMw7fR1wzJ/PQ== X-Google-Smtp-Source: AK7set9tBcoWN2oy1k5hF9OXqkN5dsgKdFyX/mrTPW2P7xozmqevHxsqeF2zJdY4p/qAlSwpEJd9qw== X-Received: by 2002:a17:903:1208:b0:19e:e001:6a75 with SMTP id l8-20020a170903120800b0019ee0016a75mr9058686plh.6.1678330729988; Wed, 08 Mar 2023 18:58:49 -0800 (PST) Received: from smtpclient.apple ([2601:646:101:7fda:5d84:d5f7:d358:64a5]) by smtp.gmail.com with ESMTPSA id jy14-20020a17090342ce00b00199023c688esm10444372plb.26.2023.03.08.18.58.49 (version=TLS1_2 cipher=ECDHE-ECDSA-AES128-GCM-SHA256 bits=128/128); Wed, 08 Mar 2023 18:58:49 -0800 (PST) From: Bryn Llewellyn Message-Id: Content-Type: multipart/alternative; boundary="Apple-Mail=_64BE8411-A382-4F7E-BA2E-A23EA3655043" Mime-Version: 1.0 (Mac OS X Mail 14.0 \(3654.120.0.1.14\)) Subject: Re: select (17, 42)::s.t2 into... fails with "invalid input syntax" Date: Wed, 8 Mar 2023 18:58:48 -0800 In-Reply-To: <281321.1678328800@sss.pgh.pa.us> Cc: pgsql-general list , "David G. Johnston" To: Tom Lane PostgreSQL References: <281321.1678328800@sss.pgh.pa.us> X-Mailer: Apple Mail (2.3654.120.0.1.14) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --Apple-Mail=_64BE8411-A382-4F7E-BA2E-A23EA3655043 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 > tgl@sss.pgh.pa.us wrote: >=20 >> bryn@yugabyte.com writes: >> select (17, 42)::s.t2 into r2; >> [ doesn't work ] >=20 > This would work as >=20 > select 17, 42 into r2; >=20 > 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=E2=80=A6 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 =3D pg_catalog, pg_temp language plpgsql as $body$ declare my_c1 int :=3D 17; my_c2 int :=3D 42; r s.x; txt text; begin r :=3D (select (a.c1, a.c2)::s.x from s.t a where a.k =3D 1); z :=3D (r.c1)::text||' / '||(r.c2)::text; = return next; select a.c1, a.c2 into r from s.t a where a.k =3D 1; z :=3D (r.c1)::text||' / '||(r.c2)::text; = return next; begin r :=3D (my_c1, my_c2)::s.t; exception when cannot_coerce then z :=3D '"cannot_coerce" handled.'; = return next; end; r.c1 :=3D my_c1; r.c2 :=3D my_c2; z :=3D (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=E2=80=94and non-composable. I wonder if it all boils down to the strange-to-me anonymous, = polymorphic "record" notion. --Apple-Mail=_64BE8411-A382-4F7E-BA2E-A23EA3655043 Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=utf-8
tgl@sss.pgh.pa.us wrote:

bryn@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=E2=80=A6 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 =3D = pg_catalog, pg_temp
  language plpgsql
as $body$
declare
  my_c1 int :=3D 17;
  my_c2 = int :=3D 42;
  r s.x;
  txt text;
begin
  r :=3D (select (a.c1, a.c2)::s.x from s.t a where = a.k =3D 1);
  z :=3D (r.c1)::text||' / = '||(r.c2)::text;               =               return next;

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

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

  r.c1 = :=3D my_c1;
  r.c2 :=3D my_c2;
  z :=3D (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=E2=80=94and non-composable.

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

= --Apple-Mail=_64BE8411-A382-4F7E-BA2E-A23EA3655043--