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