



	Das geht mit Indizes, löschen alter Daten, ... oder Materialized Views. z.B. in der Variante Materialized View - Table wird refreshed, wenn die Grunddaten sich ändern: ( Verwendung: db.refresh_materialized_viewsAsync(); dbTab = new SqlTab( db, ..., "al_storage.l_storage_lagerjournal_qry", ...) ) CREATE SEQUENCE al_storage.L_Storage_audit_dmlno_seq; grant usage on al_storage.L_Storage_audit_dmlno_seq TO al_storageextuser_role, ahp_w3_extuser_role; CREATE TABLE al_storage.L_Storage_audit ( dml_id numeric( 14, 0) NOT NULL default 0, -- dmlno numeric( 14, 0) NOT NULL, dmloperation varchar(10) NOT NULL, dmltime timestamp without time zone NOT NULL, dmluser varchar(50) , dmlclient_addr varchar(50) , -- dml_details json, -- CONSTRAINT L_Storage_audit_pk PRIMARY KEY ( dmlno ), CONSTRAINT L_Storage_audit_chk_dml_id CHECK ( dml_id = 0 ) ); grant select, update on al_storage.L_Storage_audit to al_storageonlineuser_role, al_storagerole, al_storageextuser_role; INSERT INTO al_storage.l_storage_audit( dmlno, dmloperation, dmltime ) VALUES ( 0, 'init', now() ); CREATE or replace FUNCTION al_storage.L_Storage_audit_tf() RETURNS trigger LANGUAGE 'plpgsql' AS $BODY$ DECLARE L_Storage_audit_dmlno int; L_Storage_audit_dml_details json; BEGIN IF (TG_OP = 'DELETE') THEN SELECT to_json( OLD.) into L_Storage_audit_dml_details; ELSE SELECT to_json( NEW.) into L_Storage_audit_dml_details; END IF; SELECT nextval('al_storage.L_Storage_audit_dmlno_seq') into L_Storage_audit_dmlno; update al_storage.L_Storage_audit SET DMLNO = L_Storage_audit_dmlno, dmloperation = TG_OP, dmltime = now(), dmluser = user, dmlclient_addr = inet_client_addr()::varchar(50), dml_details = L_Storage_audit_dml_details; IF (TG_OP = 'DELETE') THEN RETURN OLD; ELSE RETURN NEW; END IF; END; $BODY$; CREATE TRIGGER L_Storage_audit_aiud AFTER INSERT OR DELETE OR UPDATE ON al_storage.L_Storage FOR EACH ROW EXECUTE FUNCTION al_storage.L_Storage_audit_tf(); CREATE TRIGGER L_StoragePos_audit_aiud AFTER INSERT OR DELETE OR UPDATE ON al_storage.L_StoragePos FOR EACH ROW EXECUTE FUNCTION al_storage.L_Storage_audit_tf(); -- --- --- --- CREATE or replace FUNCTION al_storage.refresh_materialized_views() RETURNS integer SECURITY DEFINER AS $$ DECLARE audit_tab_dmlno int; lrr_mv_audit_dmlno int; BEGIN SELECT max(dmlno) from al_storage.L_Storage_audit into audit_tab_dmlno; SELECT min(audit_dmlno) from al_storage.l_storage_lagerjournal_raw_raw into lrr_mv_audit_dmlno; if audit_tab_dmlno is null or lrr_mv_audit_dmlno is null or audit_tab_dmlno <> lrr_mv_audit_dmlno then refresh materialized view CONCURRENTLY al_storage.l_storage_lagerjournal_raw_raw; return 1; end if; RETURN 0; END; $$ LANGUAGE plpgsql; ALTER FUNCTION al_storage.refresh_materialized_views() OWNER TO al_storagerole; FUNCTION al_storage.l_storage_artikelkonto_qry_refresh() TO al_storagerole WITH GRANT OPTION; -- --- --- --- create materialized view al_storage.l_storage_lagerjournal_raw_raw as select all s_audit.dmlno as audit_dmlno, STORAGE_ID, STORAGE_POS_ID, STORAGE_POS_ID as BUCH_NR, 'L' as INP_KNZ, 'Abg' AS ABG_ZUG_KNZ, ABG_LAGERORT AS LAGERORT, ZUG_LAGERORT AS GEGENBUCH_LAGERORT, BEWEGUNGS_DATUM, ARTIKELNR, ART_BEZEICHNUNG, (BEWEGUNGS_MENGE * -1) AS BEWEGUNGS_MENGE, BUCHUNGSTEXT, STORAGE_POS_details, created_at FROM al_storage.L_Storage_audit s_audit, al_storage.L_StoragePos stor_pos -- union all -- select all s_audit.dmlno as audit_dmlno, STORAGE_ID, STORAGE_POS_ID, STORAGE_POS_ID as BUCH_NR, 'L' as INP_KNZ, 'Zug' AS ABG_ZUG_KNZ, ZUG_LAGERORT AS LAGERORT, ABG_LAGERORT AS GEGENBUCH_LAGERORT, BEWEGUNGS_DATUM, ARTIKELNR, ART_BEZEICHNUNG, BEWEGUNGS_MENGE, BUCHUNGSTEXT, STORAGE_POS_details, created_at FROM al_storage.L_Storage_audit s_audit, al_storage.L_StoragePos stor_pos -- order by 1, 2 ; CREATE UNIQUE INDEX l_storage_lagerjournal_raw_raw_uidx on al_storage.l_storage_lagerjournal_raw_raw ( STORAGE_ID, STORAGE_POS_ID, ABG_ZUG_KNZ ); ALTER materialized view al_storage.l_storage_lagerjournal_raw_raw OWNER TO al_storagerole;


	...
	...