> pavel.steh...@gmail.com wrote:
>
>> t...@sss.pgh.pa.us wrote:
>>
>>> pavel.steh...@gmail.com wrote:
>>>
>>> Some errors like this, but not this can be detected by plpgsql_check
>>> https://github.com/okbob/plpgsql_check
>>> probably the heuristic for type check is not complete.
>>
>> STRICTMULTIASSIGNMENT would detect most cases of this, except that the
>> condition is checked too late. We'd need to count the fields *before*
>> trying to assign values, not after.
>>
>> In the meantime, it does seem like the docs could be more explicit about
>> this, and perhaps give an example showing the (x).* solution.
>
> Yes, a more detailed explanation of this behavior can be nice. There can be
> an example of value unnesting, but I think so for this case, there should be
> mainly an example of ANSI assign syntax.
>
> var := (SELECT x FROM ..)
>
> This syntax has advantages so is not amigonuous for this case, and explicit
> unnesting is not necessary (and it is not possible). Moreover, this is ANSI
> SQL syntax.
Consider this example:
create type type1 as (a1 int, a2 int);
create table tab1(k int primary key, b boolean not null, t type1 not null);
insert into tab1(k, b, t) values(1, true, (10, 20));
select b::text, t::text from tab1 where k = 1;
It seems to be perfectly plausible—and so it seems equally plausible that you'd
want to do it using PL/pgSQL. Each of these two alternatives, inspired by the
advice that I got in this thread, works and produces the expected output:
do $body$
declare
r record;
begin
select ((b, t)).*
into r
from tab1
where k = 1;
raise info 'Alt 1: % | %', r.f1::text, r.f2::text;
r := (
select (b, t)
from tab1
where k = 1);
raise info 'Alt 2: % | %', r.f1::text, r.f2::text;
end;
$body$;
It feels a smidge uncomfortable because I'm forced to use reference by field
position (f1, f2) rather than by field name (b, t). But reference by position
is commonplace in PostgreSQL (for example, in the PREPARE statement). So
I'spose that I have to learn to like it.
Do you (all) think that the "Alt 1" and "Alt 2" that I show are the best way to
implement the requirement? I prefer "Alt 2" because it doesn't have the clutter
(and the burden for understanding and readability) of the extra parentheses and
the ".*".
I can get the reference by field name that I prefer with a schema-level type:
create type tmp as (b text, t type1);
and by declaring "r" with this data type. But this is a greater discomfort than
using the dynamically shaped "record" because it needs you to create a
dedicated schema-level type for every new SELCT list that you come need.
