Re: Create Table Dinamic

["Anderson dos Santos Donda" <andersondonda@xxxxxxxxx>]

On Wed, Aug 6, 2008 at 11:59 PM, Craig Ringer <craig@xxxxxxxxxxxxxxxxxxxxx> wrote:

Anderson dos Santos Donda wrote:

> PS : If somebody want knows why I need to create this function, is because
> in my db have 1000 tables with the some colums, and each time I have a new
> client, I need to create this tables manually.

While EXECUTE or CREATE TABLE ... LIKE is the answer to the immediate
question, I have to ask: Is this really the best approach?

This is a bit of a design red flag, you see. Is it possible that rather
than:

CREATE TABLE x_client1(
 x_client1_id   SERIAL PRIMARY KEY,
 name           TEXT
);

CREATE TABLE x_client2(
 x_client2_id   SERIAL PRIMARY KEY,
 name           TEXT
);

CREATE TABLE x_client3(
 x_client3_id   SERIAL PRIMARY KEY,
 name           TEXT
);



... etc, you might be better off with:



CREATE TABLE client (
 client_id     SERIAL PRIMARY KEY,
 client_name   TEXT NOT NULL
 -- etc
);

CREATE TABLE x (
 x_id           SERIAL NOT NULL PRIMARY KEY,
 client_id      INTEGER NOT NULL,
 FOREIGN KEY (client_id) REFERENCES client(client_id) ON DELETE CASCADE,
 -- Then the fields from the template table for `x':
 name           TEXT,
 -- etc
);

CREATE INDEX x_client_id_fkey ON x (client_id);



?

If you are separating the tables for better control over priveleges or
the like, might it be better to create a new database instance per
client instead?

Of course, there are certainly cases where templated tables make sense.
In particular, if you need some resources shared between all users, but
other resources to be restricted by database permissions to be private
to each user, then cloned tables make sense. Putting them in per-user
schema keeps things clean and lets you use the schema search path rather
than lots of ugly table name pre/suffixes if you have to do this.

--
Craig Ringer

Thanks all for Help, and answer Craig...

Each client has a db, and each db has the same tables. I don't need to share datas with the clients ( and I can't do it ) , because each clients have differents datas in yours tables.

My function is to help me to create a new db with the tables.

I have a particular server db with the names tables of each client, so I want to do a FOR LOOP in my function to create my tables on new database.

Example

CREATE OR REPLACE FUNCTION CreateTriggersFunctionsSetValues()
RETURNS VOID AS
$BODY$
DECLARE
  NumRowsQuotes ativos%ROWTYPE;
  NameTable text := '';
BEGIN

  FOR NumRowsQuotes IN SELECT * FROM ativos
  LOOP
     SELECT INTO NameTable ativos WHERE codigo = NumRowsQuotes;
     setvalues(NameTable);
  END LOOP;
 
END;
$BODY$

LANGUAGE 'plpgsql' VOLATILE;


If there a better way to do it .. I thanks to tell me!!

Any way.. thanks for Help!!