út 10. 8. 2021 v 19:48 odesílatel Bryn Llewellyn <b...@yugabyte.com> napsal:
> pavel.steh...@gmail.com wrote: > > t...@sss.pgh.pa.us wrote: > > *pavel.steh...@gmail.com <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: > 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
