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

Поиск
Список
Период
Сортировка
От John W Higgins
Тема Re: PL/pgSQL: Spurious 22P02 error on "select col into var" when col is user-defined type
Дата
Msg-id CAPhAwGwvf1A=rCC2ZLEQMeT02tDxH=iNfhM=Wzpw-XpDV9MXEw@mail.gmail.com
обсуждение исходный текст
Ответ на PL/pgSQL: Spurious 22P02 error on "select col into var" when col is user-defined type  (Bryn Llewellyn <bryn@yugabyte.com>)
Список pgsql-general

On Mon, Aug 9, 2021 at 12:41 PM Bryn Llewellyn <bryn@yugabyte.com> 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.

"composite type represents the structure of a row or record;"


"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 

В списке pgsql-general по дате отправления:

Предыдущее
От: Matthias Apitz
Дата:
Сообщение: Re: (13.1) pg_basebackups ./. pg_verifybackup
Следующее
От: Pavel Stehule
Дата:
Сообщение: Re: PL/pgSQL: Spurious 22P02 error on "select col into var" when col is user-defined type