Re: [GENERAL] Create Table Dinamic

2008-08-08 Thread Sam Mason 
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

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Create Table Dinamic

2008-08-07 Thread Igor Neyman 
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:[EMAIL PROTECTED] 
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.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Create Table Dinamic

2008-08-07 Thread Anderson dos Santos Donda 
On Wed, Aug 6, 2008 at 11:59 PM, Craig Ringer
[EMAIL PROTECTED]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!!

Re: [GENERAL] Create Table Dinamic

2008-08-07 Thread Lennin Caro 
try whit this

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


--- On Thu, 8/7/08, Anderson dos Santos Donda [EMAIL PROTECTED] wrote:

 From: Anderson dos Santos Donda [EMAIL PROTECTED]
 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.


  


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Create Table Dinamic

2008-08-06 Thread Anderson dos Santos Donda 
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.

Re: [GENERAL] Create Table Dinamic

2008-08-06 Thread mian wang 
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 [EMAIL PROTECTED]

 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

Re: [GENERAL] Create Table Dinamic

2008-08-06 Thread Craig Ringer 
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

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general