Re: Create Table Dinamic

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