This question concerns Toad Data Modeler v6.0.3.13; database is PostgreSQL.
I’d like to define a new Dictionary Type (i.e., Postgres DOMAIN) in terms of an existing Dictionary Type. However, that isn’t supported in TDM, and the drop-down list of available types (whether intrinsic or user-defined) excludes Dictionary Types. Strictly speaking, this is a bug, as Postgres allows that (with the expected acyclic dependency caveats). In my particular case, it’s forcing me to compromise my design.
For example, the following runs just fine:
CREATE DOMAIN my_base_type
AS int;
ALTER DOMAIN my_base_type
OWNER TO postgres;
CREATE DOMAIN my_sub_type
AS my_base_type;
ALTER DOMAIN my_base_type
OWNER TO postgres;
One utility of this is feature is the ability to attach additional CONSTRAINTs to the subtype. Another is the ability to use a subtype as a way to overload functions of the same name and achieve embellished or different behavior. For example:
CREATE OR REPLACE FUNCTION mult(i my_base_type) RETURNS my_base_type AS $$
BEGIN
RETURN i * 2;
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION mult(i my_sub_type) RETURNS my_sub_type AS $$
BEGIN
RETURN i * 3;
END;
$$ LANGUAGE plpgsql;
Hello,
thank you for your suggestion. I’ve created a new enhancement request (TDM-2853) to add this feature to any future versions of TDM. In the current version of TDM you can use the following (and maybe a little inconvenient) workaround.
Create a new user data type with the same name as the first (base) domain, open its properties and uncheck the checkbox Generate. When you will create another (sub) domain you can select this user data type from the drop-down list of types as the alias for the base dictionary type.
Thank you for your reply, enhancement request, and workaround suggestion.
Unfortunately, in my case a convenience alias/typedef to the base dictionary type won’t help. I truly need a distinct domain in the type tree as I rely on it for overloading functions. So if the native type is int, the base domain type is my_int, and then the subtype of that is my_sub_int, having my_sub_int alias int prevents the use of functions overloading on params of type my_int.
That said, I have a workaround for now which is doing the trick. That is an “After Script” (separate tab for that in Dictionary pop-up) to the sub type which literally drops it and recreates aliasing the immediate parent type (and not the native type).
Appreciate your creating an enhancement request for this. Look forward to it, and in the meantime I’m good to go with my workaround.