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

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


It now seems to me to be odd, in the light of the explanations for why the naïve (PL/SQL-style) syntax doesn't work in PL/pgSQL, that assigning a scalar subquery to a variable of the composite type in question _does_ work! But don't take that as a question. I'm going to regard this as "case closed".

This depends on how integration of PL/pgSQL and SQL is designed.  PL/pgSQL is a relatively small procedural interpretation over SQL engine. When you evaluate a query, then you always get a composite value (named tuple) always (in all cases).

SELECT 10, 20 INTO rec; 

In this case you get composite (10,20) and it can be assigned to composite without problems.

SELECT (10,20) INTO rec

returns composite ((10,20)), and that cannot be assigned to your composite.

Syntax rec := (SELECT 10,20) is not possible. Subquery can return only one value always. More values are not allowed.

rec := (SELECT (10,20)) is working, because you can assign (in all cases) the first field of returned composite value. This syntax cannot be ambiguous.

If you work intensively with plpgsql, then it can be a very informative look at plpgsql source code.  Don't be afraid it is not too long, and you will see. It is very simple. Then you can understand how it works.


Regards

Pavel



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

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