On Wed, Nov 18, 2020 at 12:34 AM Post Gresql <postgresql@xxxxxxxxxxx> wrote:
or even a complete table row as return type.
As mentioned, this is already possible.
create type my_type (a int, b my_table.my_column%type);
The real reason: you will be sure you are using the same type everywhere. And it is easier to change type later on, then only one column has to be changed, not many and in a lot of different places.
The documentation for pl/pgsql says:
"By using %TYPE you don't need to know the data type of the structure you are referencing, and most importantly, if the data type of the referenced item changes in the future (for instance: you change the type of user_id from integer to real), you might not need to change your function definition."
In short, you cannot gain that benefit (avoid recompilation) at the SQL level. I believe your "change type" use case is thus rendered basically undoable. And I don't see the effort to be worth the benefit for "create type" alone.
Just write: create type my_type (a int, b int); -- b's type matches my_table.my_column
And/Or: comment on column my_type.b is '@depends on my_table.my_column%type'
David J.
