How to create shadow table in Informix

Question

I was working on an audit trail module that require me to create a huge list of shadow table from original table. I was thinking to write a stored procedure that could generate a shadow table from another table. This table could be any table, it could be table_A that consist of 3 fields of any data type, or table_B that consist of 10 fields of any data type, and that could base on the parameter pass into the store procedure. I know I can retrieve a list of fields available to the particular table_A from syscolumns and systables like this: ``` select * from syscolumns where tabid = (select tabid from systables where tabname='table_A') ``` There will be a list of fields return from this statement, lets say field_A, then I will rename this field_A to create 2 new fields which is pre_field_A and post_field_A, and then use these 2 new fields to create a shadow table of shadow_table_A. Apply this concept to the rest of the fields. Need not worry about the data type of a field because this i…

Accepted Answer

Your query will work, but it might be more idiomatic to use a join:

```
SELECT *
  FROM "informix".systables AS t
  JOIN "informix".syscolumns AS c ON t.tabid = c.tabid
 WHERE t.tabname = 'table_a';
```

Also, be aware that the system catalog will only contain `table_A` in mixed case if you created the table while you had DELIMIDENT set in the environment and you created the table with the name enclosed in double quotes. Normally, the table name will be in all lower-case letters in the system catalog; similarly for column names.

However, that's all tangential to your question. Dealing with user-defined types in all their glory is painful. However, if you're dealing with normal databases, you can certainly work like that, though it might be easier to use DB-Schema (`dbschema`) to generate the schema for a table, and then trap that. You could actually do that through a stored procedure using the SYSTEM statement, but I'd probably do it from outside a stored procedure. It does depend on what else you need to do. The pre-image and post-image for each field could be modestly costly.

If you have IBM Informix Dynamic Server 11.70, you can create your CREATE {audit} TABLE statement dynamically, and then execute the statement. So, you'd build up your query using a FOREACH loop in the stored procedure to add each column in turn, and then execute the statement to create the audit table. You'll have to decode the type, too. You could/should use a procedure for that, too. I'm assuming that `tabname` is a variable passed to the stored procedure, and `c_colno`, `c_colname`, and `c_typename` are local variables (as is `cts`, short for 'create table statement', and `pad`):

```
LET cts = 'CREATE TABLE ' || tabname || '(';
LET pad = '';
FOREACH SELECT c.colno, c.colname, type_name(c.coltype, c.collength)
          INTO c_colno, c_colname, c_typename
          FROM "informix".systables AS t
          JOIN "informix".syscolumns AS c
            ON t.tabid = c.tabid
         WHERE t.tabname = tabname
         ORDER BY c.colno
    LET cts = cts || pad || 'pre_'  || c_colname || ' ' || c_coltype;
    LET cts = cts || ',' || 'post_' || c_colname || ' ' || c_coltype;
    LET pad = ',';
END FOREACH;
LET cts = cts || ');';
```

You may want to deal with NOT NULL and primary key constraints and all sorts of other things, but this gives you the basics to be going on with.

How to create shadow table in Informix

Solution

Problem