Re: Create Table Dinamic

On Wed, Aug 6, 2008 at 11:59 PM, Craig Ringer
<craig(at)postnewspapers(dot)com(dot)au>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!!