Re: [SQL] How to creat tables using record ID in for loop

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.

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