Re: 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.

Hi:

CREATE OR REPLACE FUNCTION MakeTables ( NameTable text ) RETURNS void as
$$
BEGIN
execute 'CREATE TABLE '||$1||' ( id int, name text )';
END;
$$
LANGUAGE 'plpgsql';

2008/8/7 Anderson dos Santos Donda <andersondonda(at)gmail(dot)com>

> 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.
>

--
Kind Regards,
Mian

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(at)gmail(dot)com]
Sent: Wednesday, August 06, 2008 9:10 PM
To: pgsql-general(at)postgresql(dot)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.

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!!

try whit this

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

--- On Thu, 8/7/08, Anderson dos Santos Donda <andersondonda(at)gmail(dot)com> wrote:

> From: Anderson dos Santos Donda <andersondonda(at)gmail(dot)com>
> Subject: [GENERAL] Create Table Dinamic
> To: pgsql-general(at)postgresql(dot)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