problem temporary table
I work in :
PostgreSQL 7.2 on i686-pc-cygwin, compiled by GCC 2.95.3-5
I create a temporary table and temporary sequence and trigger.
-- temporary sequence
create temporary sequence irextab60_ord_seq start 1;
-- temporary tabella
create temporary table irextab60 (
pad int4,
fig int4,
liv int4,
lvc varchar(20),
mlv int2 default 0,
ord int4 DEFAULT nextval('irextab60_ord_seq'::text) NOT NULL
);
-- trigger
create trigger irextab60_trigger before insert on irextab60 for each
row execute procedure irextab60_explo();
-- function
CREATE FUNCTION irextab60_explo() RETURNS opaque AS '
DECLARE
ifig INTEGER;
iliv INTEGER;
ilvc VARCHAR(20);
imlv INTEGER;
-- esplosione tabella irtab
BEGIN
ifig:=NEW.fig;
iliv:=NEW.liv;
ilvc:=NEW.lvc;
imlv:=NEW.mlv;
IF imlv = 0 THEN
INSERT INTO irextab60 (pad,fig,liv,lvc,mlv)
SELECT pad, id, iliv + 1, trim(ilvc) + ''.'', imlv
FROM irtab WHERE pad = ifig ORDER BY cod;
ELSE
INSERT INTO irextab60 (pad,fig,liv,lvc,mlv)
SELECT pad, id, iliv + 1, trim(ilvc) + ''.'', imlv
FROM irtab WHERE pad = ifig and iliv<imlv ORDER BY cod;
END IF;
RETURN NEW;
END;'
LANGUAGE 'plpgsql';
This is a method to create a ricorsion table 'irtab' before insert to
irextab60.
The ricorsion is good.
My problem is when select a record for table irtab,irextab60.
The select is:
select a.id from irtab a,irextab60 b where a.id=b.fig
The velocity of selection is variable,I do not know .
I create a index when create a table irextab60 after insert into table
but the selection is not good.
create index aaa on irextab60 (fig)
I create a index when create a table irextab60 before insert into table
but the selection is not good.
create index aaa on irextab60 (fig)
Bye !!
Frank Lupo (Wolf) !!
--
Prendi GRATIS l'email universale che... risparmia: http://www.email.it/f
Sponsor:
Tutti i nuovi modelli di telefonia su Ebest
Clicca qui: http://adv2.email.it/cgi-bin/foclick.cgi?mid=550&d=25-7