how to generate database table INHERITANCE postgresql script with Powerdesigner

Question

I have two tables, "child" and "parent", "child" must inherit all columns of "parent", I use 'Generalization' link between the two tables in the conceptual model (class diagram), when I generate physical model it give me this result :

create table child (
   id                   INT4                 null,
); 

create table parent (
   id                   INT4                 null,
   name                 INT4                 null
);

alter table child
   add constraint FK_CHILD_GENERALIZ_PARENT foreign key (id)
      references parent(id)
      on delete restrict on update restrict;

But in Postgresql inheritance is not a FK constraint, it must give me :

create table child (

); INHERITS (parent);

What I miss ??

Whatever "PowerDesigner" is, it clearly doesn't understand PostgreSQL's table inheritance scheme, and has given you the way the same thing is implemented in standard SQL. — Craig Ringer, Jul 16 '13 at 05:24

score 1 · Answer 1 · answered Jul 17 '13 at 12:05

It actually seems that the inheritance information is not kept when generating the Postgresql Physical Model from the Conceptual Model.

As an exercise (for fun, you might say), I devised a crude solution...

Have an editable version of the Postgres DBMS (use the standard one, or create a copy, or embed it in the physical model)
In this DBMS, add a Transformation "PreserveInherits" under Profile > Table (see below)
In the DBMS, add a Transformation Profile "PreserveInheritance", with Model type "Conceptual Data Model", and a step in Post-generation using the above "PreserveInherits" transformation
When using Tools > Generate Physical Data Model in the CDM, make sure "Enable transformations" is pushed/checked in the "Detail" tab of the PDM Generation Options
The trick generates the desired "inherits(foo)" option, by overriding the Physical Options on the tables.

The source of the PreserveInherits transformation:

dim targetmap
dim targetmapcreation

sub DescentTargets(pkg, trfm)
   dim obj
   for each obj in pkg.tables
      if not (obj.IsShortcut) then
         dim src : set src = trfm.GetSource(obj)
         if not (src is nothing) then targetmap.add src,obj
       end if
   next
   for each obj in pkg.Packages
      DescentTargets obj,trfm
   next
end sub

function GetTargetObject(obj, model, trfm)
   ' global dims are not reset betweeen generations, use a timer to reset it sometimes
   if isempty(targetmap) or datediff("s",targetmapcreation,time)>60 then
      set targetmap = CreateObject("Scripting.Dictionary")
      targetmapcreation = time
      ' fill map with information about target objects
      DescentTargets model,trfm
   end if
   if targetmap.Exists(obj) then set GetTargetObject = targetmap.Item(obj)
end function

Sub %Transformation%(table, trfm)
   ' find origin entity
   dim source : set source = trfm.GetSource(table)
   if not (source is nothing) and source.IsShortcut then set source = source.TargetObject
   if not (source is nothing) and source.ClassKind = cls_Entity then
     ' walk up to parent entity
      dim link
      for each link in source.InheritsFrom
         dim cdmparent : set cdmparent = link.ParentEntity
         if not (cdmparent is nothing) and cdmparent.IsShortcut then cdmparent = cdmparent.TargetObject
         if not (cdmparent is nothing) then
            ' walk "back" to target table of parent entity
            ' trfm.GetParent(cdmparent) does not work as I hoped, use a helper function
            dim pdmparent : set pdmparent = GetTargetObject(cdmparent,table.model,trfm)
            if not (pdmparent is nothing) then
               ' msgbox "found parent table " & pdmparent.name
               table.physicaloptions = "inherits (" & pdmparent.code & ")"
            end if
         end if
         ' TODO modify the code to deal with several inheritances...
         exit for
      next
   end if
End Sub

how to generate database table INHERITANCE postgresql script with Powerdesigner

1 Answers1