Re: trigger instead of view

Cesar: This is a case of the old adage "when all you have is a hammer, everything starts to look like a nail." SPL is the wrong tool to solve the problem of being too lazy to hand code alters to multiple table schema as well as an INSTEAD OF trigger for a view over those tables. Also, I think that your idea of multiple tables is the wrong tool to solve the original problem, but I'll come back to that one at the end. What I would do is to write a shell script possibly combined with an awk or perl script that it calls (or do the whole thing in perl/ror/etc) that takes the name and type of the new column you want to add and outputs the alter statements for all of the tables and outputs your drop procedure ....; create procedure....; statements. The procedure itself should either have a section for the insert into each table or build the insert, as you have done below, but you have to build the actual values of the incoming 'new' structure into the insert string not the variable names of the structure elements. What I would do is to leave a marker in comments in the procedure code so that your script can just insert the code for the new column(s) into the procedure at the correct location by editing the existing version of the procedure. So, the procedure would look, in part, something like this: ... let lSQL="insert into h_dbases_"||lData||" values ( "; let ISQL=rtrim(lSQL) || "'" || n.frstcol || "'"; ... let lSQL=rtrim(lSQL) || ",'" || n.lstcol || "'"; { New Cols Insert Here } let lSQL=rtrim(lSQL) || ");"; execute immediate lSQL; ... Then your script will edit that basic SQL script, which you can either save or generate on the fly using dbschema or myschema. Here's a simplfied awk version: awk '/New Cols Insert Here/{ printf "let lSQL=rtrim(lSQL) || \\",\\"\\"|| n.%s || \\"\\"\\";\\n", newcol; }{ print $0; }' -v newcol=$1 <oldproc.sql >newproc.sql This will insert a new 'value' into the insert statement between the last previously existing value and the marker comment. Now, onto my other comment. Check out timeseries for this. It seems to me that what you have is a regular timeseries! Altering the contnt columns of the rowtype defining the timeseries will be a bit more complex, but I expect that will be rare and the performance improvement during inserts and queries will more than make up for that. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Tue, Nov 20, 2012 at 7:58 AM, Cesar Inacio Martins < cesar_inacio_martins@yahoo.com.br> wrote: > Hi, > > ifx 11.70 fc6it , Linux > > Need a little help to solve a situation what I don't found a decent > workaround. > > I'm was executing few tests over the situation bellow , trying > "simulate" a fragmentation/partition using view + trigger... > Just for fun and as possible solution for a database with Innovator-C > which my desire is use it to keep historical data of some collected > statistics. > And to facility the load of data, I want use the view for the "inserts" > DMLs. > > * 1 view , result of many selects with union all. > This tables have the same schema and they differ is only the period > of the data, (each table = 1 month) > something like : > drop view if exists vw_dbases; > create view if not exists vw_dbases as > select * from h_dbases_2011_01 where datini >= '2011-01-01' and> datini < '2011-02-01' > union all > select * from h_dbases_2011_02 where datini >= '2011-02-01' and> datini < '2011-03-01' > union all > select * from h_dbases_2011_03 where datini >= '2011-03-01' and> datini < '2011-04-01' > union all > ... > > * 1 insert trigger with instead of view > I call a SPL to give the properly treatment . HERE is the difficult. > I want wrote a code what identify dynamically the date (year-month) and > execute the insert into the correct table , other desire is don't > have a > schema dependency (suppose I change the schema of this tables adding > a new > column in the middle, the trigger keeps works and identifying the new > fields, > working as 4GL, "LIKE variable.*". > > Identify the date and execute the insert into the correct table is > the easy > part. The problem is inform the values. > The table have a considerable amount of columns and is very very very > annoying > and hard work describe each field, casting it to CHAR and treat each > NULL > value... > Let's to code, what I not found a easy workaround : > > drop procedure if exists sp_trg_vw_dbases ; > create procedure if not exists sp_trg_vw_dbases() > referencing old as o new as n for vw_dbases > define lSQL char(800);> define lData char(7); > -- set debug file to '/tmp/cesar.out' ; > -- trace on; > let lData = n.datini::datetime year to month ; > > -- Here is the code what I desire to work... obvious..don't. > -- how workaround this !??? > let lSQL="insert into h_dbases_"||lData||" values ( n.* )" ; > execute immediate lSQL ; > > > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list >