> > >> 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 >
static composite types can be good when you use dynamic SQL. The plpgsql_check cannot derive output composite type from dynamic SQL. And it can stop checking. When you use static composite type, then the check can continue. Regards Pavel > Regards > > Pavel >
