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

út 10. 8. 2021 v 19:48 odesílatel Bryn Llewellyn <bryn(at)yugabyte(dot)com> napsal:

> pavel(dot)stehule(at)gmail(dot)com wrote:
>
> tgl(at)sss(dot)pgh(dot)pa(dot)us wrote:
>
> *pavel(dot)stehule(at)gmail(dot)com <pavel(dot)stehule(at)gmail(dot)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:
>

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

>
> 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 prefer Alt 2 too.

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

When It is possible I use a record type - some years ago, the work with
this type was a little bit slower, but not now. The work with this type is
little bit safer - because it gets real labels. Values with declared
composite types uses positional assignment, that is not too safe.

create table foo(a int, b int);
create type footype as (a int, b int);
insert into foo values(10, 200);

postgres=# do $$
declare f footype;
r record;
begin
select b, a from foo into f;
select b, a from foo into r;
raise notice 'f.a: %, f.b: %', f.a, f.b;
raise notice 'r.a: %, r.b: %', r.a, r.b;
end;
$$;
NOTICE: f.a: 200, f.b: 10
NOTICE: r.a: 10, r.b: 200
DO
postgres=# select * from foo;
┌────┬─────┐
│ a │ b │
╞════╪═════╡
│ 10 │ 200 │
└────┴─────┘
(1 row)

But sometimes explicit type is necessary - when you want to return
composite value and when you want to work with composite outside function,
or when you want to serialize, or deserialize composite value to/from json.

When you work with composite values, is good to enable warnings

https://www.postgresql.org/docs/current/plpgsql-development-tips.html#PLPGSQL-EXTRA-CHECKS

Regards

Pavel