Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 





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




[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux