Re: Create Table Dinamic - Mailing list pgsql-general

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