From: Bryn Llewellyn <bryn@yugabyte.com>
To: Tom Lane PostgreSQL <tgl@sss.pgh.pa.us>
Cc: pgsql-general list <pgsql-general@lists.postgresql.org>
Cc: David G. Johnston <david.g.johnston@gmail.com>
Subject: Re: select (17, 42)::s.t2 into... fails with "invalid input syntax"
Date: Wed, 8 Mar 2023 18:58:48 -0800
Message-ID: <E9868EBC-B655-4DFE-9DC7-940C3D55339D@yugabyte.com> (raw)
In-Reply-To: <281321.1678328800@sss.pgh.pa.us>
References: <C68A39DC-A728-41A2-ADD7-A004DB4156FD@yugabyte.com>
<281321.1678328800@sss.pgh.pa.us>
> 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… 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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-general@postgresql.org
Cc: bryn@yugabyte.com, tgl@sss.pgh.pa.us, david.g.johnston@gmail.com
Subject: Re: select (17, 42)::s.t2 into... fails with "invalid input syntax"
In-Reply-To: <E9868EBC-B655-4DFE-9DC7-940C3D55339D@yugabyte.com>
References: <C68A39DC-A728-41A2-ADD7-A004DB4156FD@yugabyte.com> <281321.1678328800@sss.pgh.pa.us> <E9868EBC-B655-4DFE-9DC7-940C3D55339D@yugabyte.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox