Thread: Create tables based on another table

Hi all,

I want to have a table that lists all other tables in a database. For
example:


table Project_List (
    project_id   integer;
    project_name text;
      project_type text;  -- can have values simple/complex
);

Now I want to automatically create a new table in the database whenever an
entry is made in this "Project_List". Based on "project_type", this new
table could have 2 different structures.


if $project_type is "simple"

    create table $project_name (     -- I want this to be filled in
                                     -- from the new row just added
                                     -- in the above table
        file_id integer;
        file_name text;
    );
else

    create table $project_name (

        file_id integer;
        file_name text;
        file_chksum text;
    );
fi


What mechanism can I use to accomplish this ? Could anyone point me to any
documentation ?

Thanks,
-ansh

On Sun, 2003-09-21 at 19:23, Anshuman Kanwar wrote:
> Hi all,
>
> I want to have a table that lists all other tables in a database. For
> example:
,,,
>
> Now I want to automatically create a new table in the database whenever an
> entry is made in this "Project_List". Based on "project_type", this new
> table could have 2 different structures.
...
> What mechanism can I use to accomplish this ? Could anyone point me to any
> documentation ?

You need a trigger that runs after every INSERT.  If the data can change
and affect the subsidiary tables as a result, you may also need triggers
to run after UPDATE and/or DELETE.

See:
CREATE TRIGGER
CREATE FUNCTION
Procedural languages: PL/pgSQL (especially the chapter on using this
language in a trigger)

--
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight, UK                             http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C
                 ========================================
     "Therefore when thou doest thine alms, do not sound a
      trumpet before thee, as the hypocrites do in the
      synagogues and in the streets, that they may have
      glory of men. Verily I say unto you, They have their
      reward. But when thou doest alms, let not thy left
      hand know what thy right hand doeth; That thine alms
      may be in secret; and thy Father which seeth in secret
      himself shall reward thee openly."       Matthew 6:2-4