Re: create type with %type or %rowtype

On Wed, Nov 18, 2020 at 12:34 AM Post Gresql <postgresql(at)taljaren(dot)se> 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.