Thread: 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

Read about dynamic sql in Postgres documentation (EXECUTE statement):

http://www.postgresql.org/docs/8.2/static/plpgsql-statements.html

Igor

-----Original Message-----
From: Anderson dos Santos Donda [mailto:andersondonda@gmail.com]
Sent: Wednesday, August 06, 2008 9:10 PM
To: pgsql-general@postgresql.org
Subject: Create Table Dinamic

Hello All!

Its my first time here in maillist and I started work with postgre on
last moth.

My questions is: Threre is a way to create tables dinamic?

Example:

To create a table we use CREATE TABLE TableName ......

In my db, I have many tables with diferents names but with same colums

Example:

TableOne ( id int, name text );
TableTwo ( id int, name text );
TableThree ( id int, name text );

So, I created a function to create me this tables with diferents names

CREATE OR REPLACE FUNCTION MakeTables ( NameTable text ) RETURNS VOID $$
BEGIN
  CREATE TABLE NameTable ( id int, name text ); END; $$ LANGUAGE
'plpgsql';

But, the plpgsql or postgre don't accept this..

So, How can I create a table with my function?

Thanks for any helps!!!

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.

try whit this

http://www.postgresql.org/docs/8.3/interactive/ecpg-dynamic.html


--- On Thu, 8/7/08, Anderson dos Santos Donda <andersondonda@gmail.com> wrote:

> From: Anderson dos Santos Donda <andersondonda@gmail.com>
> Subject: [GENERAL] Create Table Dinamic
> To: pgsql-general@postgresql.org
> Date: Thursday, August 7, 2008, 1:10 AM
> Hello All!
>
> Its my first time here in maillist and I started work with
> postgre on last
> moth.
>
> My questions is: Threre is a way to create tables dinamic?
>
> Example:
>
> To create a table we use CREATE TABLE TableName ......
>
> In my db, I have many tables with diferents names but with
> same colums
>
> Example:
>
> TableOne ( id int, name text );
> TableTwo ( id int, name text );
> TableThree ( id int, name text );
>
> So, I created a function to create me this tables with
> diferents names
>
> CREATE OR REPLACE FUNCTION MakeTables ( NameTable text )
> RETURNS VOID
> $$
> BEGIN
>   CREATE TABLE NameTable ( id int, name text );
> END;
> $$
> LANGUAGE 'plpgsql';
>
> But, the plpgsql or postgre don't accept this..
>
> So, How can I create a table with my function?
>
> Thanks for any helps!!!
>
> 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.

On Thu, Aug 07, 2008 at 10:55:06AM -0300, Anderson dos Santos Donda wrote:
> 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.

You may want to look into the "template" parameter of CREATE DATABASE.
Whenever a database is created it's actually just copied from an
existing database.  It normally comes from "template1", which is a
basically empty and clean database that it's initialized when the
cluster is created (installed).  If you have lots of databases that are
basically the same and unchanging, you may want to create the tables in
one database (say "clienttemplate") and do:

  CREATE DATABASE client101 TEMPLATE 'clienttemplate';

and all the tables/views/stored procedures/other definitions in
the template will be automatically copied into the new database.
One caveat, is that this is a once only operation.  Once the new
database has been created, the link back to the template is lost so
any subsequent changes in the template won't also happen in the new
database.  For more details have a look at [1].


  Sam

 [1] http://www.postgresql.org/docs/current/static/sql-createdatabase.html