CREATE TABLE with a name derived from a string
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
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 indexed it
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:
Show quoted text
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
On 12 Feb 2003, 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 (...);
I think using execute might work:
EXECUTE ''CREATE TABLE History'' || $1 || '' (...);'';
I came across the EXECUTE command while googling for a solution to this
problem but found
a) it was incredibly hard to read because of all the quotes and
b) I couldn't find much documentation for it. It isn't mentioned on the main
red hat postgreSQL manual pages at all
(http://www.redhat.com/docs/manuals/database/) and the word EXECUTE isn't
the best word to google on :). The best resource for plpgsql I could find is
at http://www.phpfreaks.com/postgresqlmanual/page/plpgsql.html but I wasn't
sure if this was going to help me. Are there any better resources for
plpgsql? I'll look into it more now anyway. Thanks.
Meantime, I got a reply from Dmitry Tkach yesterday that made me reconsider
why we are taking this approach. Short term I no longer need this
functionality, but will be needing it over the coming weeks for definite.
Thanks,
John
_____
John Savage <mailto:jsavage@data-mate.com>
Software Engineer DataMate Global Communications
Tel: +1 818 487 3900 ext105
_____
-----Original Message-----
From: Stephan Szabo [mailto:sszabo@megazone23.bigpanda.com]
Sent: Friday, February 14, 2003 10:36 AM
To: Savage
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] CREATE TABLE with a name derived from a string
On 12 Feb 2003, 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 (...);
I think using execute might work:
EXECUTE ''CREATE TABLE History'' || $1 || '' (...);'';
On Fri, 14 Feb 2003, John Savage wrote:
I came across the EXECUTE command while googling for a solution to this
problem but found
a) it was incredibly hard to read because of all the quotes and
b) I couldn't find much documentation for it. It isn't mentioned on the main
red hat postgreSQL manual pages at all
(http://www.redhat.com/docs/manuals/database/) and the word EXECUTE isn't
the best word to google on :). The best resource for plpgsql I could find is
at http://www.phpfreaks.com/postgresqlmanual/page/plpgsql.html but I wasn't
sure if this was going to help me. Are there any better resources for
plpgsql? I'll look into it more now anyway. Thanks.
I don't know if techdocs has anything more useful, otherwise the official
interactive docs have a short section. (Programmer's Guide)
The short form is that for most general queries you want to run you can
make a string that contains the full query and then EXECUTE that string.
JSavage@data-mate.com (Savage) writes:
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.
As others have mentioned this is a bad idea. A really really bad idea.
On option is to just put userid first in your primary key and use it on all
lookups. That's what indexes are for.
--
greg