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.
