On Mon, Aug 9, 2021 at 12:41 PM Bryn Llewellyn <bryn@xxxxxxxxxxxx> wrote:
Question 1.-----------Where, in the PL/pgSQL doc, does it state that "select col into var" when col is a user-defined type doesn't work—and where is the viable approach shown?
The first paragraph of the SO answer completely explains why this occurs.
However, the following 2 locations explain how we get here
Opening sentence of that page.
"A composite type represents the structure of a row or record;"
2) https://www.postgresql.org/docs/current/plpgsql-statements.html - Section 42.5.3
"The result of a SQL command yielding a single row (possibly of multiple columns) can be assigned to a record variable, row-type variable, or list of scalar variables."
You did not provide a scalar variable - you provided a composite type - which equates to a record/row-type variable and therefore, as described, the engine tried to place each column returned into a column of your composite type. Therefore the first column of the select result is placed in the first column of your composite type - and you get an error.
It would seem rather clear that a sentence discussing composite types is very much an option here in 42.5.3 to clarify it further given your confusion today.
Question 2.-----------If I can easily re-write a failing approach by hand (once I know that I must) so that it works, why cannot the PL/pgSQL compiler do this under the covers?
First, there is no need to not write the select almost the way you initially tried. The following works just fine.
select (r1).h, (r1).w
into r
from t1
where k = 1;
into r
from t1
where k = 1;
The engine sees a composite type as the receiver and places the data in it as it is supposed to.
As another example, switch out your do with the following and it works fine. It's not user-defined types - but rather how they work.
create type rect_bucket as (r rect);
do $body$
declare
r rect_bucket;
begin
select r1
into r
from t1
where k = 1;
end;
$body$;
do $body$
declare
r rect_bucket;
begin
select r1
into r
from t1
where k = 1;
end;
$body$;
Since the composite type is a single column of rect type - the select into works - as does the variant I showed earlier.
John
