út 10. 8. 2021 v 21:25 odesílatel Pavel Stehule <pavel.steh...@gmail.com> napsal:
> >>> 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. >> > There are a lot of use cases for static composite types. Everywhere on the interface. http://okbob.blogspot.com/2013/10/using-custom-composite-types-in.html >> 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 >> >
