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

From Pavel Stehule
Subject Re: PL/pgSQL: Spurious 22P02 error on "select col into var" when col is user-defined type
Date
Msg-id CAFj8pRDwC=UN9=_jXuRDLFcqaAFdHP6YrOTgz_9Gxu4ZL9m8ew@mail.gmail.com
Whole thread Raw
In response to Re: PL/pgSQL: Spurious 22P02 error on "select col into var" when col is user-defined type  (Bryn Llewellyn <bryn@yugabyte.com>)
List 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 by date:

Previous
From: Pavel Stehule
Date:
Subject: Re: PL/pgSQL: Spurious 22P02 error on "select col into var" when col is user-defined type
Next
From: Matthias Apitz
Date:
Subject: Re: (13.1) pg_basebackups ./. pg_verifybackup