> [email protected] wrote:
>
> [email protected] <mailto:[email protected]> wrote:
>
>> [email protected] <mailto:[email protected]> wrote:
>>
>>> [email protected] <mailto:[email protected]> wrote:
>>>
>>>> [email protected] <mailto:[email protected]> wrote:
>>>>
>>>> Some errors like this, but not this can be detected by plpgsql_check
>>>> https://github.com/okbob/plpgsql_check
>>>> <https://www.google.com/url?q=https://github.com/okbob/plpgsql_check&source=gmail-imap&ust=1629227921000000&usg=AOvVaw3Et9tiGoSScn4bG0DPyF8J>
>>>> 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:
>
> Yes, this works. This syntax is not ambiguous.
>
> 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.
>
> postgres=# do $$
> declare r record;
> begin
> select 10 as a, 20 as b into r;
> raise notice '% %', r.a, r.b;
> end;
> $$;
> NOTICE: 10 20
> DO
>
> The composite value always has structure, and types, but sometimes it can
> lose labels. You can push labels by casting
>
> r := (select (b, t) -- this is dynamic composity value, but without labels -
> the scalar value doesn't hold label
>
> or
>
> r := (select (b, t)::type1 -- it is composite with labels again
Thanks, but I don't understand your "r := (select (b, t)::type1 -- it is
composite with labels again". I tried this:
create procedure p(i in int)
language plpgsql
as $body$
declare
r record;
begin
case i
when 1 then
select (b, t)::type1
into r
from tab1
where k = 1;
when 2 then
r := (
select (b, t)::type1
from tab1
where k = 1);
else null;
end case;
end;
$body$;
call p(3);
call p(2);
call p(1);
My idea with using a procedure and choosing which code path is followed at
run-time is to distinguish between compile-time errors (there are none here)
and run-time errors. Of course, "call p(3)" finishes with no error.
But both the other calls cause the same error:
42846: cannot cast type record to type1
But you say that this should work!
