6

I want to export my model to a PostgreSQL database. If I do so as is, the objects are built in the Public schema, because the model doesn't specify a schema, and Public happens to be the default. Does anyone know a way to specify a schema in PowerDesigner?

I can change the default schema in the database, but that seems a little cheesy to me. I ought to be able to control that in my modeling tool, it seems to me.

Henry
  • 600
  • 2
  • 7
  • 22

1 Answers1

5

PDM Model Options

Go to the Tools Menu

Go to Model Options

Under Category>Model Settings>Table & View

Then you'll see Default owner on the right side.

Response to comment

PD is a great tool because it's very easy to try out simple cases. Follow these steps.

  • Create a new PDM for PostGRES
  • Add a table_1 (to it add columns_1, columns_2, columns_3)
  • Add a new user called DBO (make sure to set the NAME and the CODE to DBO)
  • Make the change I describe to the model options
  • Add a table_2 (to it add columns_1, columns_2, columns_3)

Now right click on the PDM in the browser pane and choose the preview tab.

You'll see: this

Notice how the preview for table_2 has DBO. in front of the table name EXACTLY as you desire. I've also included in the screencap the screen for the List of Tables. You get to that via the Model menu. Notice how the owner is set to the DBO user for table_2, exactly like in the previewed DDL. If you go into the properties for table_1 or use this screen to change ALL of your tables en masse, all of your DDL will work the way you want.

XDB File

create [%Temporary% ]table [%QUALIFIER%]%TABLE% (
   %TABLDEFN%
 )
 [%OPTIONS%]

Not sure what the %QUALIFIER% variable gets filled with but it seems to work.

Stephanie Page
  • 3,875
  • 1
  • 18
  • 22
  • That just creates a user, not specify the schema. What I'm looking for is a way to prepend the schema to all of the object names in the create statements generated in the DDL created when I Generate the database. So, for example, as things are now, table "table_1" will Generate a DDL statement like: create table table_1 (). This will create the table in the default schema, i.e. public. I want to be able to specify the schema so that the DDL reads: create table schema.table_1. – Henry Jul 26 '11 at 11:37
  • This will do that. But it only sets the Default for NEW tables, for existing tables you need to change their owner. I'll expand the answer. – Stephanie Page Jul 26 '11 at 19:43
  • It's not quite working that way for me. What version of PDM are you using? Ours is 15.0.0.2613. When I follow your steps, I do get the owner assigned to the tables, but it doesn't appear as part of the table name in the create statement, even on new tables created after I define the default user. I do get alter table statements, setting the owner of the table to the new user, but it does not become part of the create statement. I'm going to hunt around the help and see if there's a setting somewhere that might enable that behavior. I appreciate your help with this. – Henry Jul 27 '11 at 11:33
  • I think I got it. I have to modify the CREATE statement syntax in the DBMS Properties. By adding %OWNER%. to the create table statement, I get what I need. It's still too bad that I can't use a real SCHEMA definition, because I don't really want these things owned by that user. It's funny that the idea of schema doesn't seem to exist anywhere in the Postgres 8 DMBS. Oh, well. Now I have to decide whether I want to use the user to get this schema specification or not. In any case, at least I now know how to get close to where I want to be. Thanks for your help. – Henry Jul 27 '11 at 12:15
  • I'm on 15.3.something. If you do what I did and it doesn't work... open a ticket with Sybase (if you can). let me check my xdb file to see if that is there because THAT is exactly how it would work. – Stephanie Page Jul 27 '11 at 14:26