AFAIK, there is no way to do this in either sql or plpgsql (may be the latter is possible, but I don't think so).
You could do that in C (for sure), or, maybe, in plperl (I don't know about that) if you really want to...
But the more important thing is - I don't think you do (want to to do it).
Whatever you think the benefit of splitting that stuff into separate tables is, it is an illusion.
Just create a single table, with a userid column, and thow all of your history there. As long as you have it properly
indexedit
will perform better then those multiple tables you are trying to generate (and will also be a lot easier to maintain).
I hope, it helps...
Dima.
Savage wrote:
> I am creating a DB for a multi user IM system. Each user can store
> their history. Each user also has a unique ID. There will be many
> users and a lot of history will be generated by each user, so we are
> going to create a history table per user, with a tablename that is
> derived from the unique userid. I've hit a problem implementing this
> scheme.
>
> I want to be able to write a stored procedure in postgres that takes
> an integer userid as a param and creates a table called
> History.<userid>. (e.g. History.695). My dream is:
>
> CREATE TABLE 'History' || $1 (...);
>
> but this won't work. I dabbled with plpgsql but didn't get very far
> (I'm not an expert at this stuff :( ). I also tried the ALTER TABLE XX
> RENAME TO YY but this won't allow strings for the table names either
> :(.
>
> I know that this could be done relatively easily be creating the
> queries in C++ and executing them, but I want to do as much as
> possible in stored procedures for many reasons (performance, reuse,
> abstraction from DB changes...).
>
> Has anyone any experience doing something similar? Any help or
> pointers would be appreciated.
>
> Thanks,
> John
