Re: PL/pgSQL: Spurious 22P02 error on "select col into var" when col is user-defined type

On Mon, Aug 9, 2021 at 12:41 PM Bryn Llewellyn <bryn(at)yugabyte(dot)com> wrote:

>
> *https://stackoverflow.com/questions/41843016/how-do-i-pass-a-user-defined-type-variable-to-a-function-as-a-parameter
> <https://stackoverflow.com/questions/41843016/how-do-i-pass-a-user-defined-type-variable-to-a-function-as-a-parameter>*
>
> *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

1) https://www.postgresql.org/docs/current/rowtypes.html

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;

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$;

Since the composite type is a single column of rect type - the select into
works - as does the variant I showed earlier.

John