App-Detail #1014 zeitliche Optimierung von Views 
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;
##2072 demo-online -- -
- 1 -    (TxtDoc-optimierung-views2026-04-08.html)

					
					download