Thread: How to creat tables using record ID in for loop

1. I have created the first table and inserted records as below<br />CREATE TABLE mytest (<br />    staid
varchar(20),<br/>    kdesc  varchar(50)          -- description of gage station <br />    )<br />    WITHOUT OIDS;<br
/><br/><br /> INSERT INTO mytest VALUES ('96784002', 'mylocation #1');<br />INSERT INTO mytest VALUES ('02385067',
'mylocation#2');<br />INSERT INTO mytest VALUES ('01734056', 'mylocation #3');<br />INSERT INTO mytest VALUES
('04784097','mylocation #4');<br />INSERT INTO mytest VALUES ('16784201', 'mylocation #5');<br /><br /> 2. The records
looklike this.<br /><br /> SELECT * FROM mytest;<br /><br /> ++++++++++++++++++++++++++<br />"96784002";"mylocation
#1"<br/>"02385067";"mylocation #2"<br />"01734056";"mylocation #3"<br />"04784097";"mylocation #4"<br
/>"16784201";"mylocation#5"<br />++++++++++++++++++++++++++<br /><br /> 3. What I want to do is to create tables with
staidfrom mytest table using for loop.<br />But, since the name of tables should be in text format, I added a string
's'to staid. <br />Thus, the name of table should be like this s96784002 because of 's' +'staid'.<br /><br
/>+++Example+++<br/><br /> CREATE TABLE s06784000 (<br />    staid   varchar(50),<br />    val           
real,         -- streamflow<br />    date            date<br />)<br /><br /> WITHOUT OIDS;<br /><br /><br /><br /> 4. I
mustcreate tables using for loop because I have millions of records. <br /><br />I appreciate your help in advance. <br
/><br/><br />Jae<br /><br /><br /><br /><br /><hr />강력해진 보안성, 아웃룩을 닮아 편리해진 기능들로 무장한 Windows Live Hotmail! <a
href="http://www.hotmail.com"target="_new">뜨거운 메일 핫메일, Windows Live Hotmail로 돌아오다!</a> 

On mið, 2008-08-06 at 18:52 +0000, CHUNRIMACHUNRIMA wrote:
> "96784002";"mylocation #1"
> "02385067";"mylocation #2"
> "01734056";"mylocation #3"
...
> 3. What I want to do is to create tables with staid from mytest table
> using for loop.
...
> +++Example+++
> 
> CREATE TABLE s06784000 (
>     staid   varchar(50),
>     val            real,          -- streamflow
>     date            date
> )
> 
> WITHOUT OIDS;

what about a query that generates texts like  'CREATE TABLE s06784000 .....;'
for each row of your table? then you can either
feed the output to psql, or just EXECUTE them
in a PL/pgSQL function.

> 
> 4. I must create tables using for loop because I have millions of
> records. 

you want to create millions of tables? you should do a bit of
performance testing before you commit to this, as I
imagine that you might get into scalability problems.

gnari

The function to treate tables is:

CREATE OR REPLACE FUNCTION cr_tbls_by_staid() RETURNS INTEGER AS
$$
DECLARE stid INTEGER; q TEXT;
BEGIN FOR stid IN SELECT staid FROM mytest LOOP   q:= 'CREATE TABLE "s' || staid || '" (staid varchar(50), val real,
dt date);'   RAISE NOTICE 'query is: %', q; --for debug   EXECUTE q; END LOOP; RETURN 1;
END;
$$ LANUAGE plpgsql;

However, there are some advices with regards to your question. First,
as Rangar noted, you could fall into scalability issue when create a
couple of millions of tables. You probably should to examine data
design for your DB. For example, you could create the only table like
this:

CREATE TABLE sta_descs (staid varchar(50), val real, dt date) WITHOUT OIDS;

and store there all records you want. Such a table could be easily
joined with mytest by staid. Obviosly, sta_desct.staid have to be
indexed.

Second, take a look at SQL syntax to figure it out how the tables and
fields could be named:
http://www.postgresql.org/docs/8.3/interactive/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS

PS. I didn't test is function works properly as I have no access to
PgDB right now.

HTH
-- 
Best regards. Yuri.

Oh, I checked the function. There are some syntax errors. Right code
listed below:

CREATE OR REPLACE FUNCTION cr_tbls_by_staid() RETURNS INTEGER AS
$$
DECLAREstid INTEGER;q TEXT;
BEGINFOR stid IN SELECT staid FROM mytest LOOP  q:= 'CREATE TABLE "s' || staid || '" (staid varchar(50), val real,
dt date);';  RAISE NOTICE 'query is: %', q; --for debug  EXECUTE q;END LOOP;RETURN 1;
END;
$$ LANGUAGE plpgsql;

Also I forget to change first column definition in last CREATE TABLE
query. Edited variant is, for sure:
CREATE TABLE sta_descs (staid INTEGER, val real, dt date) WITHOUT OIDS;

-- 
Best regards. Yuri.
mailto: yuragal@gmail.com