die Zieltabelle, enthält ca. 2500 Einzelbäume unter root, aber das ist ja eigentlich egal…
CREATE TABLE d_data.d_attribut
(
roid bigserial NOT NULL, -- ID
tree_id integer, -- Baum ID
rank integer NOT NULL, -- Ebene
lft bigint NOT NULL, -- links
rgt bigint NOT NULL, -- rechts
comments character varying, -- Bemerkung, Kommentar
y_m_obj_roid bigint, -- roid aus y_meta_object
fadd_user character varying DEFAULT "current_user"(), -- Benutzer des Eintragens - first add user
fadd timestamp with time zone DEFAULT now(), -- Zeitstempel des Eintragens - first add
k_objit_char_roid bigint,
attribut character varying,
CONSTRAINT pk_attribut_neu PRIMARY KEY (roid),
CONSTRAINT k_objit_char_d_att FOREIGN KEY (k_objit_char_roid)
REFERENCES y_metadata.k_objitem_char (roid) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT y_m_obj_d_att FOREIGN KEY (y_m_obj_roid)
REFERENCES y_metadata.y_meta_object (roid) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
OIDS=FALSE
);
CREATE INDEX attindda
ON d_data.d_attribut
USING btree
(attribut);
CREATE INDEX index_k_objit_char
ON d_data.d_attribut
USING btree
(k_objit_char_roid);
CREATE INDEX index_rank
ON d_data.d_attribut
USING btree
(rank);
CREATE INDEX index_tree_id
ON d_data.d_attribut
USING btree
(tree_id);
CREATE INDEX index_y_m_obj
ON d_data.d_attribut
USING btree
(y_m_obj_roid);
CREATE INDEX indroidda
ON d_data.d_attribut
USING btree
(roid);
CREATE INDEX lftindexda
ON d_data.d_attribut
USING btree
(lft);
CREATE INDEX rgtindda
ON d_data.d_attribut
USING btree
(rgt);
Wie zu sehen ist, habe ich mittlerweile auf jede erdenkliche Spalte einen Index gelegt.
Die Quelltabelle kann varieren, es sind aber normale Tabellen mit bis zu 40 Spalten und mehreren tausend Datensätzen, sie enthalten alle nur eine Primärschlüssel.
Die Funktion die das alles erledigen soll:
CREATE OR REPLACE FUNCTION y_function.insert_d_attribut_b2til()
RETURNS void AS
$BODY$
DECLARE
ersterloop text;
zweiterloop text;
insertme text;
refcur record;
roid_par integer;
roid_koic integer;
roid_obj integer;
tabellenschema text;
tabellenname text;
methode bigint;
refcur_plot record;
tabobj_roid integer;
attributaustabelle character varying;
BEGIN
tabellenschema:='quellschema';
tabellenname:='quelltabelle';
methode := 233;--Vorklärung
ersterloop:='select column_name from information_schema.columns where table_schema=' || quote_literal(tabellenschema) || ' and table_name=' || quote_literal(tabellenname);
zweiterloop:='select attribut::integer from d_data.d_attribut where y_m_obj_roid=40 order by attribut::integer DESC';
/*welche spalten hat die tabelle?*/
for refcur in execute ersterloop LOOP
for refcur_plot in execute zweiterloop LOOP
/* HIER WERDEN DIE PARAMETER GESUCHT */
roid_par:=roid from y_metadata.y_meta_parameter where (select split_part(ref_l, ' ## ',1))=tabellenname and (select split_part(ref_l, ' ## ',2))=refcur.column_name;
roid_koic:= koic.roid from y_metadata.k_objitem_char koic inner join y_metadata.k_object_item koi on koic.k_obj_item_roid=koi.roid inner join y_metadata.k_item_ref kir on koi.k_item_roid=kir.roid where kir.meth_roid=methode and para_roid=roid_par;
roid_obj:=koi.m_ort_roid from y_metadata.k_object_item koi inner join y_metadata.k_item_ref kir on koi.k_item_roid=kir.roid where kir.meth_roid=methode and para_roid=roid_par;
--for refcur_subplot in select * from d_data.d_attribut where lft>=(SELECT lft from d_data.d_attribut where attribut::integer=refcur_plot.attribut) and rgt<=(SELECT rgt from d_data.d_attribut where attribut::integer=refcur_plot.attribut) LOOP
tabobj_roid:=roid from d_data.d_attribut where y_m_obj_roid=roid_obj and k_objit_char_roid is null and lft>=(SELECT lft from d_data.d_attribut where attribut='' || refcur_plot.attribut || '' and k_objit_char_roid is null) and rgt<=(SELECT rgt from d_data.d_attribut where attribut='' || refcur_plot.attribut || '' and k_objit_char_roid is null);
/*diese Abfrage muss an jede Tabelle angepasst werden, um den richtigen Wert zum Plot zu finden */
execute 'select ' || refcur.column_name || ' from ' || quote_ident(tabellenschema) || '.' ||quote_ident(tabellenname) || ' where num=' || refcur_plot.attribut || ';' into attributaustabelle;
if attributaustabelle is null then
raise notice 'attributtabelle is null, überspringe';
else
attributaustabelle:='''' || attributaustabelle || '''';
insertme:='select y_function.x_da_attribut_unterhalb_rein(''d_data'', ''d_attribut'',' || tabobj_roid || ',null, null,' || roid_obj || ', ' || roid_koic || ', ' || attributaustabelle || ');';
raise notice 'bin bei spalte: % num: % wert: %',refcur.column_name,refcur_plot.attribut,attributaustabelle;
--raise notice '%',insertme;
execute insertme;
end if;
end loop; --refcur_plot
end loop;--refcur
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE
COST 100;
Es ist sicherlich etwas schwer zu verstehen, da die Funktion, die nachher aufgerufen werden soll mit Parametern gefüttert werden muss, die aus verschiedenen Tabellen kommen. Aber hier noch die augerufene Nestedset-Funktion die unterhalb einfügt:
CREATE OR REPLACE FUNCTION y_function.x_da_attribut_unterhalb_rein(character varying, character varying, bigint, bigint, character varying, bigint, bigint, character varying)
RETURNS void AS
$BODY$
/*
$1=zielschema
$2=zieltabelle
$3=zielroid unter der eingefügt werden soll
$4=tree_id (baum_id);Standard: NULL --> wenn Tabelle keine Spalte baum_id dann nicht berücksichtigt; wenn Spalte baum_id vorhanden: bei NULL wird baum_id von zielroid($3) benutzt, bei 1 wird nächsthöhere baum_id erzeugt
$5=comments
$6=y_m_obj_roid
$7=k_objit_char_roid
$8=attribut
*/
DECLARE
ziel_schema ALIAS FOR $1;
ziel_tabelle alias for $2;
ziel_roid alias for $3;
tree_id_neu alias for $4;
quelle_comments alias for $5;
quelle_y_m_obj_roid alias for $6;
quelle_k_objit_char_roid alias for $7;
attribut alias for $8;
baum_id_exists integer;
suche_baum_id text;
suche_baum_id_int integer;
insert_ort text;
runme1 text;
runme2 text;
runme3 text;
runme4 text;
runme5 text;
lastroid integer;
curatt refcursor;
/*spalten mit möglichen null-werten*/
rec_comments character varying;
rec_k_objit_char_roid character varying;
rec_attribut character varying;
d_attribut text;
BEGIN
/*NULL-Werte filtern*/
if quelle_comments is null then
rec_comments := 'NULL';
else
rec_comments := '''' || quelle_comments || '''';
end if;
if attribut is null then
rec_attribut := 'NULL';
else
rec_attribut := '''' || attribut || '''';
end if;
if quelle_k_objit_char_roid is null then
rec_k_objit_char_roid:= 'NULL';
else
rec_k_objit_char_roid:=quelle_k_objit_char_roid;
end if;
/* erstelle abfragen */
runme1:='UPDATE ' || ziel_schema || '.' || ziel_tabelle || ' SET lft = lft+2 WHERE lft > (SELECT rgt FROM ' || ziel_schema || '. ' || ziel_tabelle || ' WHERE roid=' || ziel_roid || ');';
runme2:='UPDATE ' || ziel_schema || '.' || ziel_tabelle || ' SET rgt = rgt + 2 WHERE rgt >= (SELECT rgt FROM ' || ziel_schema || '. ' || ziel_tabelle || ' WHERE roid=' || ziel_roid || ') ;';
runme3:= 'INSERT INTO ' || ziel_schema || '.' || ziel_tabelle || ' (rank,lft,rgt,comments,y_m_obj_roid,k_objit_char_roid,attribut ';
runme4 := ')VALUES
(
(SELECT rank FROM ' || ziel_schema || '.' || ziel_tabelle || ' WHERE roid=' || ziel_roid || ')+1,--rank
(SELECT rgt FROM ' || ziel_schema || '.' || ziel_tabelle || ' WHERE roid=' || ziel_roid || ')-2,--lft
(SELECT rgt FROM ' || ziel_schema || '.' || ziel_tabelle || ' WHERE roid=' || ziel_roid || ')-1,--rgt
' || rec_comments || ', --comments
' || quelle_y_m_obj_roid || ',--y_meta_object roid
' || rec_k_objit_char_roid || ',--k_objitem_char roid
' || rec_attribut || '
';--bestandteil von runme3
runme5 := ');';--bestandteil von runme3
if tree_id_neu is null then
/*baum_id von zielroid wird übernommen */
runme3 := runme3 || ',tree_id' || runme4;
runme3 := runme3 || ',(SELECT tree_id FROM ' || ziel_schema || '.' || ziel_tabelle || ' WHERE roid=' || ziel_roid || ')';
runme3 := runme3 || runme5;
elseif tree_id_neu=0 then
/*baum_id von zielroid wird übernommen */
runme3 := runme3 || ',tree_id' || runme4;
runme3 := runme3 || ',(SELECT tree_id FROM ' || ziel_schema || '.' || ziel_tabelle || ' WHERE roid=' || ziel_roid || ')';
runme3 := runme3 || runme5;
elseif tree_id_neu=1 then
--raise notice 'baum_id wird erzeugt';
runme3 := runme3 || ',tree_id' || runme4 || ',(SELECT max(tree_id) FROM ' || ziel_schema || '.' || ziel_tabelle || ' )+1' || runme5;
else
end if;
--raise notice 'runme1 jetzt';
execute runme1;
--raise notice 'runme2 jetzt';
execute runme2;
--raise notice 'runme3 jetzt';
execute runme3;
--raise notice 'runme3: %', runme3;
end;
$BODY$
LANGUAGE 'plpgsql' VOLATILE
COST 100;