CREATE TABLE with a name derived from a string

From: JSavage(at)data-mate(dot)com (Savage)
To: pgsql-general(at)postgresql(dot)org
Subject: CREATE TABLE with a name derived from a string
Date: 2003-02-13 03:21:20
Message-ID: c23c6cdc.0302121921.3150f1a3@posting.google.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Ananda K Venkataraman 2003-02-13 03:25:43 Linux on PowerPc
Previous Message Greg Stark 2003-02-13 02:23:24 Re: Is there anything equivalent to Oracle9i's list partitioning?