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

>
>
>
> 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.

https://github.com/postgres/postgres/tree/master/src/pl/plpgsql/src

Regards

Pavel