SELECT TSystem.Settings__Set('PRODAT', '23.24.08.06'); -- für DBUpdates Sync


--DELETE FROM fieldalias;
--SELECT tsystem.syncro__fieldalias();
--SELECT tsystem.syncro__dbupdates();

DO $do$
BEGIN

ALTER TABLE tablefieldinfo ADD COLUMN IF NOT EXISTS foreign_schema varchar(100);
ALTER TABLE tablefieldinfo ADD COLUMN IF NOT EXISTS   foreign_table         VARCHAR(100);           -- Die Spalte referenziert auf diese Tabelle ...
ALTER TABLE tablefieldinfo ADD COLUMN IF NOT EXISTS   foreign_column        VARCHAR(50);            -- ... und Spalte
ALTER TABLE tablefieldinfo ADD COLUMN IF NOT EXISTS   isWritable            BOOLEAN DEFAULT false;

ALTER TABLE recnogroup ADD COLUMN IF NOT EXISTS reg_gruppe_textno      INTEGER;
ALTER TABLE recnogroup ADD COLUMN IF NOT EXISTS reg_bez_textno         INTEGER;

ALTER TABLE SettingsDyn ADD COLUMN IF NOT EXISTS sd_deleted      BOOLEAN DEFAULT False;

ALTER TABLE gridlayout ADD COLUMN IF NOT EXISTS gl_default        BOOLEAN NOT NULL DEFAULT true;

ALTER TABLE adk ADD COLUMN IF NOT EXISTS ad_dokversand INTEGER;

ALTER TABLE abzu ADD COLUMN IF NOT EXISTS abz_visible           BOOLEAN NOT NULL DEFAULT TRUE;
ALTER TABLE ldsabzu ADD COLUMN IF NOT EXISTS ldaz_visible       BOOLEAN NOT NULL DEFAULT TRUE;


	BEGIN
		CREATE EXTENSION IF NOT EXISTS dblink;
    EXCEPTION
		WHEN others THEN
       		RAISE WARNING '%', SQLERRM;
	END; 

	BEGIN

CREATE FUNCTION TSystem.Settings__ENUM__Set(vari VARCHAR, value VARCHAR) RETURNS VOID AS $$
  BEGIN
    PERFORM TSystem.Settings__Set(vari, TSystem.ENUM_SetValue(TSystem.Settings__Get(vari), value));
    RETURN;
  END$$LANGUAGE plpgsql;
	
    EXCEPTION
		WHEN others THEN
       		RAISE WARNING '%', SQLERRM;
	END; 

	BEGIN

CREATE FUNCTION TSystem.Settings__ENUM__Set(vari VARCHAR, value VARCHAR, enabled BOOL) RETURNS VOID AS $$
  BEGIN
    IF enabled THEN
      PERFORM TSystem.Settings__Set(vari, TSystem.ENUM_SetValue(TSystem.Settings__Get(vari), value));
    ELSE
      PERFORM TSystem.Settings__Set(vari, TSystem.ENUM_DelValue(TSystem.Settings__Get(vari), value));
    END IF;
    RETURN;
  END$$LANGUAGE plpgsql;	
	
    EXCEPTION
		WHEN others THEN
       		RAISE WARNING '%', SQLERRM;
	END; 

	BEGIN

CREATE FUNCTION TSystem.Settings__Set(vari VARCHAR, inha BOOL) RETURNS VOID AS $$
  BEGIN
    IF inha IS NULL THEN
        PERFORM TSystem.Settings__Delete(vari);
        RETURN;
    END IF;
    IF inha THEN
        PERFORM TSystem.Settings__Set(vari, '-1');  -- true
    ELSE
        PERFORM TSystem.Settings__Set(vari, '0');   -- false
    END IF;

    RETURN;
  END $$ LANGUAGE plpgsql;	
	
    EXCEPTION
		WHEN others THEN
       		RAISE WARNING '%', SQLERRM;
	END; 

	BEGIN

-- Setting als VARCHAR setzen
CREATE FUNCTION TSystem.Settings__Set(vari VARCHAR, inha VARCHAR) RETURNS VOID AS $$
  DECLARE num NUMERIC;
  BEGIN
    IF inha IS NULL THEN
        PERFORM TSystem.Settings__Delete(vari);
        RETURN;
    END IF;
    IF EXISTS(SELECT true FROM public.settings WHERE s_inha = inha AND s_vari = vari) THEN
        RETURN;
    END IF;
    BEGIN
        num:= inha::NUMERIC(20,8); -- Cast auf max. Numeric versuchen. Länge entspr. Standard für interne Werte.
    EXCEPTION
        WHEN OTHERS THEN num:= NULL;
    END;
    UPDATE settings SET s_inha= inha, s_num_inha= num WHERE s_vari = vari;
    IF NOT FOUND THEN
        INSERT INTO settings (s_vari, s_inha, s_num_inha) VALUES (vari, inha, num);
    END IF;

    RETURN;
  END $$ LANGUAGE plpgsql;	
	
    EXCEPTION
		WHEN others THEN
       		RAISE WARNING '%', SQLERRM;
	END; 

	BEGIN

CREATE FUNCTION TSystem.Settings__GetText(vari varchar, defvalue varchar DEFAULT '')
    RETURNS text
    AS $$
        SELECT coalesce(
                    (SELECT s_inha FROM public.settings WHERE s_vari = vari)
                    , defvalue
               );
    $$ LANGUAGE sql PARALLEL SAFE;
	
    EXCEPTION
		WHEN others THEN
       		RAISE WARNING '%', SQLERRM;
	END; 

	BEGIN

-- Hinweis: Begrenzung von den 8191 Zeichen, im PgDAC, bei vom VARCHAR ohne Längenangabe.
CREATE FUNCTION TSystem.Settings__Get(vari varchar)
    RETURNS varchar
    AS $$
        SELECT TSystem.Settings__GetText(vari);
    $$LANGUAGE sql PARALLEL SAFE;
	
    EXCEPTION
		WHEN others THEN
       		RAISE WARNING '%', SQLERRM;
	END; 

	BEGIN

CREATE FUNCTION TSystem.Settings__ENUM__Get(vari varchar, value varchar) RETURNS bool AS $$
    BEGIN
        RETURN TSystem.ENUM_GetValue(TSystem.Settings__Get(vari), value);
    END$$LANGUAGE plpgsql PARALLEL SAFE;
	
    EXCEPTION
		WHEN others THEN
       		RAISE WARNING '%', SQLERRM;
	END; 

	BEGIN

CREATE FUNCTION TSystem.Settings__ENUM__Get(vari varchar) RETURNS SETOF varchar AS $$
    BEGIN
        RETURN QUERY SELECT unnest(string_to_array(coalesce(TSystem.Settings__Get(vari), ''), ',')::varchar[]);
    END$$LANGUAGE plpgsql PARALLEL SAFE;
	
    EXCEPTION
		WHEN others THEN
       		RAISE WARNING '%', SQLERRM;
	END; 

	BEGIN

CREATE FUNCTION TSystem.Settings__GetInteger(vari varchar, defvalue integer DEFAULT 0) RETURNS integer AS $$
    DECLARE r varchar;
    BEGIN
        SELECT s_inha INTO r FROM public.settings WHERE s_vari=vari;
        IF NOT public.isnumeric(r) THEN --public wegen dump/restore
               RETURN defvalue;
        END IF;

        RETURN coalesce(CAST(r AS integer), defvalue);
    -- ist "parallel unsafe", da es die parallel unsafe Funktion IsNumeric verwendet (#21507)
    END $$ LANGUAGE plpgsql; -- TODO ab PG16 auf parallel safe zurück siehe IsNumeric (https://www.postgresql.org/docs/devel/functions-info.html#FUNCTIONS-INFO-VALIDITY)

    EXCEPTION
		WHEN others THEN
       		RAISE WARNING '%', SQLERRM;
	END; 

	BEGIN

CREATE FUNCTION TSystem.Settings__GetNumeric(vari varchar) RETURNS numeric AS $$
    DECLARE r numeric;
    BEGIN
      BEGIN
          SELECT coalesce(s_num_inha, s_inha::numeric) INTO r FROM public.settings WHERE s_vari = vari;
      EXCEPTION
          WHEN OTHERS THEN
              BEGIN
                  RETURN 0;
              END;
      END;

      RETURN coalesce(r, 0);
    END $$ LANGUAGE plpgsql PARALLEL SAFE;
	
    EXCEPTION
		WHEN others THEN
       		RAISE WARNING '%', SQLERRM;
	END; 

	BEGIN

CREATE FUNCTION TSystem.Settings__GetBool(vari varchar, defvalue boolean DEFAULT false) RETURNS boolean AS $$
    DECLARE r boolean;
            s varchar;
    BEGIN
       SELECT s_inha INTO s FROM public.settings WHERE s_vari = vari;
       --
       IF NOT FOUND THEN
              RETURN defvalue;
       END IF;
       --
       IF UPPER(s) IN ('-1','1', 'T', 'TRUE') THEN
              RETURN true;
       ELSE
              RETURN false;
       END IF;
    END$$LANGUAGE plpgsql PARALLEL SAFE;
	
    EXCEPTION
		WHEN others THEN
       		RAISE WARNING '%', SQLERRM;
	END; 

	BEGIN


	
    EXCEPTION
		WHEN others THEN
       		RAISE WARNING '%', SQLERRM;
	END; 

	BEGIN

ALTER TABLE dbupdates ALTER COLUMN upd_bez TYPE varchar(150);

    EXCEPTION
		WHEN others THEN
       		RAISE WARNING '%', SQLERRM;
	END; 

	BEGIN

CREATE OR REPLACE FUNCTION tsystem.quote_literal__connstr_param(
    IN _text varchar
  ) RETURNS varchar AS $$
  DECLARE
    _result varchar;
  BEGIN
    _result := REPLACE( REPLACE( _text, E'\\', E'\\\\' ), E'\'', E'\\\'');
    _result := concat(  '''',
                        _result,
                        ''''
                      );
    RETURN _result;
  END; $$ LANGUAGE plpgsql;

    EXCEPTION
		WHEN others THEN
       		RAISE WARNING '%', SQLERRM;
	END; 

	BEGIN

CREATE FUNCTION TSystem.views__all__drop() RETURNS void AS $$
  BEGIN
    RETURN;
  END $$ LANGUAGE plpgsql;
  
    EXCEPTION
		WHEN others THEN
       		RAISE WARNING '%', SQLERRM;
	END; 

	BEGIN

CREATE FUNCTION TSystem.views__all__create() RETURNS VOID AS $$
  BEGIN
     RETURN;
  END $$ LANGUAGE plpgsql;
  
    EXCEPTION
		WHEN others THEN
       		RAISE WARNING '%', SQLERRM;
	END; 

	BEGIN

CREATE FUNCTION TSystem.views__Adressen__recreate() RETURNS VOID AS $$
    BEGIN
	  DROP VIEW IF EXISTS adressen_view CASCADE;
	  
	  CREATE OR REPLACE VIEW adressen_view AS
      SELECT
        dbrid,
        ad_krz AS adk_ad_krz,
        ad_krz,
        ad_fa1,
        ad_fa2,
        ad_adrzus,
        ad_anr,
        ad_titel,
        ad_name,
        ad_vorn,
        ad_str,
        ad_plz,
        ad_ort,
        ad_landiso::VARCHAR(5),
        ad_land,
        ad_pfc,
        ad_fax,
        ad_tel1,
        ad_tel2,
        ad_email1,
        ad_email2,
        ad_such,
        ad_ustidnr,
        ad_branche,
        ad_stat,
        ad_bem,
        adk.modified_date,
        ad_auslauf
      FROM adk;
      RETURN;
    END $$ LANGUAGE plpgsql;
	
    EXCEPTION
		WHEN others THEN
       		RAISE WARNING '%', SQLERRM;
	END; 

	BEGIN

SELECT TSystem.views__Adressen__recreate();	

    EXCEPTION
		WHEN others THEN
       		RAISE WARNING '%', SQLERRM;
	END; 


	BEGIN

CREATE SCHEMA IF NOT EXISTS tCache;

    EXCEPTION
		WHEN others THEN
       		RAISE WARNING '%', SQLERRM;
	END; 

	BEGIN

 CREATE FUNCTION TCache.CachedViews_Clear() RETURNS BOOLEAN AS $$
    DECLARE
        r RECORD;
        e BOOLEAN;
    BEGIN
      RETURN false;
    END $$ LANGUAGE plpgsql;

    EXCEPTION
		WHEN others THEN
       		RAISE WARNING '%', SQLERRM;
	END; 

	BEGIN

 CREATE FUNCTION table_delete() RETURNS TRIGGER AS $$
    BEGIN
      RETURN old;
    END $$ LANGUAGE plpgsql;	

    EXCEPTION
		WHEN others THEN
       		RAISE WARNING '%', SQLERRM;
	END; 

	BEGIN

CREATE FUNCTION TSystem.views__ReportingAuftg__recreate() RETURNS VOID AS $$
BEGIN
    DROP VIEW IF EXISTS treporting.auftg_beleg_positionennachtrag;
    DROP VIEW IF EXISTS treporting.auftg_beleg_positionen;
END $$ LANGUAGE plpgsql;

    EXCEPTION
		WHEN others THEN
       		RAISE WARNING '%', SQLERRM;
	END; 

	BEGIN

CREATE FUNCTION treporting.recreatereportingauftgviews(	)    RETURNS void AS $$
BEGIN
	RETURN;
END $$ LANGUAGE plpgsql;

    EXCEPTION
		WHEN others THEN
       		RAISE WARNING '%', SQLERRM;
	END; 

	BEGIN

CREATE OR REPLACE FUNCTION  treporting.RecreateViews()    RETURNS void AS $$
BEGIN
	RETURN;
END $$ LANGUAGE plpgsql;

    EXCEPTION
		WHEN others THEN
       		RAISE WARNING '%', SQLERRM;
	END; 

	BEGIN

CREATE OR REPLACE FUNCTION  TWawi.RecreateViews()    RETURNS void AS $$
BEGIN
	RETURN;
END $$ LANGUAGE plpgsql;

    EXCEPTION
		WHEN others THEN
       		RAISE WARNING '%', SQLERRM;
	END; 

	BEGIN

CREATE OR REPLACE FUNCTION DropBelegViews() RETURNS VOID AS $$
BEGIN
  DROP VIEW IF EXISTS eingrech;
  DROP VIEW IF EXISTS eingrech_pos;
  DROP VIEW IF EXISTS lieferschein;
  DROP VIEW IF EXISTS lieferschein_pos;
END $$ LANGUAGE plpgsql;

    EXCEPTION
		WHEN others THEN
       		RAISE WARNING '%', SQLERRM;
	END; 

	BEGIN

 CREATE FUNCTION lang_versart(INTEGER, VARCHAR) RETURNS VARCHAR AS $$    -- #7137
   BEGIN
   RETURN null;
  END $$ LANGUAGE plpgsql;
  
    EXCEPTION
		WHEN others THEN
       		RAISE WARNING '%', SQLERRM;
	END; 

	BEGIN

  -- Value aus einem INI-TEXT auslesen -> [Section] Name=Value
  CREATE FUNCTION TSystem.INI_GetValue(ini TEXT, section VARCHAR, name VARCHAR, defvalue VARCHAR DEFAULT NULL) RETURNS VARCHAR AS $$
  DECLARE
    sectionsinhalt TEXT;
    val VARCHAR;
  BEGIN
    --Inhalt der eingegebenen 'section' ablesen oder 'defvalue' nehmen, wenn nicht gefunden
    SELECT trim(substring(ini FROM E'\\[' || section || E'\\]([^\\[]*)'), E'\r\n\t ') INTO sectionsinhalt;
    IF sectionsinhalt IS NULL THEN
      RETURN defvalue;
    END IF;
    --Value mit dem 'name' aus der 'section' ablesen oder 'defvalue' nehmen, wenn nicht gefunden
    SELECT COALESCE(trim(substring(sectionsinhalt FROM E'[$\r\n]*' || name || E'[ \t]*=[ \t]*([^\r\n$]*)'), E'\r\n\t '), defvalue) INTO val;
    RETURN val;
  END $$ LANGUAGE plpgsql IMMUTABLE;

    EXCEPTION
		WHEN others THEN
       		RAISE WARNING '%', SQLERRM;
	END; 

	BEGIN

CREATE FUNCTION tsystem.dokutypes__find__by__parentnode_id( _parentnode_id varchar )
  RETURNS varchar
  AS $$
     SELECT null
  $$ LANGUAGE sql;  


    EXCEPTION
		WHEN others THEN
       		RAISE WARNING '%', SQLERRM;
	END; 


	BEGIN

CREATE FUNCTION tsystem.database__triggers_inaktiv__get(
    IN _all_schemas   boolean = false
    )
    RETURNS TABLE (
      tgname          varchar,
      src_schemaname  varchar,
      src_tablename   varchar,
      trg_schemaname  varchar,
      trg_tablename   varchar
    )
    AS $$
          -- hinweis: cast as varchar nur in pg kleiner 13?!
          SELECT tgname::varchar,
                 src_schema.nspname::varchar  AS src_schemaname,
                 src_table.relname::varchar   AS src_tablename,
                 trg_schema.nspname::varchar  AS trg_schemaname,
                 trg_table.relname::varchar   AS trg_tablename
            FROM pg_trigger
            LEFT JOIN pg_class      AS src_table  ON src_table.oid  = pg_trigger.tgrelid
            LEFT JOIN pg_class      AS trg_table  ON trg_table.oid  = pg_trigger.tgconstrrelid
            LEFT JOIN pg_namespace  AS src_schema ON src_schema.oid = src_table.relnamespace
            LEFT JOIN pg_namespace  AS trg_schema ON trg_schema.oid = trg_table.relnamespace
           WHERE tgenabled = 'D'
             AND (
                  -- Prüfung auf deaktivierte Trigger für alle Schemata
                      _all_schemas
                  -- Prüfung auf deaktivierte Trigger gilt nicht für folg. Schemata
                    -- ignore:    trigger on drop,   fkey: public -> drop, drop -> public, drop -> drop
                    -- relevant:  trigger on public, fkey: public -> public
                  OR  (
                       -- Quelle des deaktivierten Triggers bzw. Contraint-Triggers (fKey) ist nicht in folg. Schemata
                           coalesce( src_schema.nspname, '' ) NOT IN ( 'z_99_drop', 'x_950_import', 'x_900_export' )
                       -- analog für Ziel des Contraint-Triggers (fKey)
                       AND coalesce( trg_schema.nspname, '' ) NOT IN ( 'z_99_drop', 'x_950_import', 'x_900_export' )
                      )
                 )
           ORDER BY tgname
    $$ LANGUAGE sql;

	
    EXCEPTION
		WHEN others THEN
       		RAISE WARNING '%', SQLERRM;
	END; 


	BEGIN

CREATE FUNCTION TSystem.triggers__all__disable() RETURNS VOID AS $$
  DECLARE r RECORD;
  BEGIN
    BEGIN
      PERFORM TSystem.Settings__Set('triggersdisabled', True);
    EXCEPTION WHEN OTHERS THEN
      PERFORM TSystem.Settings__Set('triggersdisabled', True);
    END;
	
    FOR r IN
      --SELECT DISTINCT schemaname, tablename
      --FROM tablefieldinfo
      --WHERE schemaname NOT IN ('z_99_drop', 'import')
      --  AND tablename NOT IN ('tablefieldinfo')  -- es gibt ein SELECT darauf, drum lässt sich kein ALTER TABLE machen
      --  AND NOT isView
      SELECT DISTINCT table_schema AS schemaname, table_name AS tablename
      FROM information_schema.tables
      WHERE table_catalog = current_database()
        AND table_type = 'BASE TABLE'
        AND table_schema NOT IN ('information_schema', 'pg_catalog', 'z_99_drop', 'import')
    LOOP
      EXECUTE 'ALTER TABLE ' || quote_ident(r.schemaname) || '.' || quote_ident(r.tablename) || ' DISABLE TRIGGER ALL';
    END LOOP;
  END $$ LANGUAGE plpgsql;

	
    EXCEPTION
		WHEN others THEN
       		RAISE WARNING '%', SQLERRM;
	END; 


	BEGIN

CREATE FUNCTION TSystem.triggers__all__enable() RETURNS VOID AS $$
  DECLARE r RECORD;
  BEGIN
    FOR r IN  -- aus TFormDBUpdates.Up_EnableTrigger -> lokales CodeSelect
      SELECT DISTINCT table_schema AS schemaname, table_name AS tablename
      FROM information_schema.tables
      WHERE table_catalog = current_database()
        AND table_type = 'BASE TABLE'
        AND table_schema NOT IN ('information_schema', 'pg_catalog', 'z_99_drop', 'import')
    LOOP
      EXECUTE 'ALTER TABLE ' || quote_ident(r.schemaname) || '.' || quote_ident(r.tablename) || ' ENABLE TRIGGER ALL';
    END LOOP;

    BEGIN
      PERFORM TSystem.Settings__Set('triggersdisabled', False);
    EXCEPTION WHEN OTHERS THEN
      PERFORM TSystem.Settings__Set('triggersdisabled', False);
    END;
  END $$ LANGUAGE plpgsql;		

	
    EXCEPTION
		WHEN others THEN
       		RAISE WARNING '%', SQLERRM;
	END; 


	BEGIN

    CREATE FUNCTION TSystem.LogDebug(source VARCHAR, message TEXT, category INTEGER DEFAULT 0, context TEXT DEFAULT NULL) RETURNS VOID AS $$
      BEGIN
        RETURN;
      END $$ LANGUAGE plpgsql;

	
    EXCEPTION
		WHEN others THEN
       		RAISE WARNING '%', SQLERRM;
	END; 


	BEGIN


	
    EXCEPTION
		WHEN others THEN
       		RAISE WARNING '%', SQLERRM;
	END; 


	BEGIN


	
    EXCEPTION
		WHEN others THEN
       		RAISE WARNING '%', SQLERRM;
	END; 


	BEGIN


	
    EXCEPTION
		WHEN others THEN
       		RAISE WARNING '%', SQLERRM;
	END; 


	BEGIN


	
    EXCEPTION
		WHEN others THEN
       		RAISE WARNING '%', SQLERRM;
	END; 


	BEGIN
	
    EXCEPTION
		WHEN others THEN
       		RAISE WARNING '%', SQLERRM;
	END; 


	BEGIN

DROP VIEW IF EXISTS TSystem.tablefieldinfo_view;
CREATE VIEW TSystem.tablefieldinfo_view AS

  SELECT
    -- this filters out composite indicies and duplicated FKs on same columns
    DISTINCT ON ( n.nspname, c.relname, a.attnum )

    -- this function is used by the information schema views
    -- the bitmask works like this:
    --    b'00000100' = deleteable
    --    b'00001000' = updateable
    --    b'00010000' = insertable
    (pg_relation_is_updatable(c.oid::regclass, false)::bit(8) & b'00011100' = b'00011100' ) AS isWritable,

    c.relkind = 'v' AS isview,
    n.nspname       AS schemaname,
    c.relname       AS tablename,
    a.attnum        AS index,
    a.attname       AS field,

    -- t.typcategory,
    format_type( a.atttypid, null ) AS type,

    -- length only for string types
    CASE WHEN
        -- S = varchars und chars
        t.typcategory IN ( 'S' )
      THEN nullif(a.atttypmod,-1) - 4
      ELSE null
    END
    AS length,

    -- adsrc was removed in pg12
    pg_get_expr( d.adbin, d.adrelid ) AS "default",

    fn.nspname AS foreign_table_schema,
    ft.relname AS foreign_table_name,
    fc.attname AS foreign_column_name

  FROM
    pg_class                  AS c

    -- schemata
    LEFT JOIN pg_namespace    AS n ON
          n.oid = c.relnamespace

    -- columns
    LEFT JOIN pg_attribute    AS a ON
          c.oid = a.attrelid

    -- columntypes
    LEFT JOIN pg_type         AS t ON
          t.oid = a.atttypid

    -- default values of columns
    LEFT JOIN pg_attrdef      AS d ON
          d.adrelid = c.oid
      AND d.adnum = a.attnum

    -- FKs
    LEFT JOIN pg_constraint   AS f ON
          f.contype = 'f'             -- foreignkey
      AND f.conrelid = c.oid          -- local table
      AND array_length ( f.confkey , 1 ) < 2 -- exclude composite keys
      AND f.conkey[1] = a.attnum      -- conkey is a array over the attributes ordinal positions

    -- destination of FKs
    LEFT JOIN pg_attribute    AS fc ON
          f.confrelid  = fc.attrelid -- foreign table
      AND f.confkey[1] = fc.attnum

    LEFT JOIN pg_class        AS ft ON
          ft.oid = f.confrelid

    LEFT JOIN pg_namespace    AS fn ON
          fn.oid = ft.relnamespace

  WHERE
    -- only tables and views ( including materialized views )
        c.relkind IN ('r','v','m')
    -- p = permanent table ( default tables)
    -- u = unlogged table ( eg. tlog.auditlog )
    -- t = temporary table ( pg_temp_%, create temp table )
    AND c.relpersistence IN ('p','u')
    -- exclude certain namespaces; they are lowercase by default, except for doubleQuoted Identifier
    AND n.nspname NOT IN (
         'pg_catalog', 'information_schema', 'tcache', 'tsystem', 'sunext', 'import', 'z_99_drop', 'x_900_export'
       )
    AND a.attnum > 0
    -- they have been deleted anyways by the TSystem.tablefieldinfo__recreate() function
    AND a.attname NOT IN ('insert_by', 'modified_by', 'insert_date', 'modified_date')
    -- we must omit dropped columns
    AND a.attisdropped IS false

  ORDER BY schemaname, tablename, index
;
--
	
    EXCEPTION
		WHEN others THEN
       		RAISE WARNING '%', SQLERRM;
	END; 


	BEGIN

-- Ausgelagert aus Delphi. Ergänzt um Weitergabe der Constraints zugrundeliegender Tabelle an die Views.

CREATE FUNCTION TSystem.tablefieldinfo__recreate(OUT fieldCountBefore INTEGER, OUT fieldCountAfter INTEGER) RETURNS RECORD AS $$
  BEGIN
    FieldCountBefore := COUNT(*) FROM tablefieldinfo;
    -- Einfach alles rauswerfen und neu aufbauen aus den Information-Schema Katalogen.
    TRUNCATE tablefieldinfo;
    INSERT INTO tablefieldinfo ( isWritable, isView, schemaname, tablename, index, field, type, length, def, foreign_schema, foreign_table, foreign_column)
    SELECT * FROM TSystem.TableFieldInfo_view ORDER BY schemaname, tablename, field;
    -- View ist Updatable und wird als ChildLink benutzt. Also brauchen wir Defaults und Foreign-Keys.
    PERFORM TSystem.CopyConstraintInfo('public', 'oplpm_data', 'public', 'oplpm');
    PERFORM TSystem.CopyConstraintInfo('public', 'oplpm_stam', 'public', 'oplpm_data');
    -- view dbrid default wert setzen
    UPDATE tablefieldinfo SET def = 'nextval(''db_id_seq'')' WHERE isView AND isWritable AND field = 'dbrid';
    FieldCountAfter :=  COUNT(*) FROM tablefieldinfo;
    --- #18294 Auditlog-Config-FieldList Refresh
    --PERFORM tlog.AuditlogConfig_OF__all__recreate();
    RETURN;
  END $$ LANGUAGE plpgsql VOLATILE;

	
    EXCEPTION
		WHEN others THEN
       		RAISE WARNING '%', SQLERRM;
	END; 


	BEGIN

  CREATE FUNCTION dbupdates__filtered__nodes__upd_ids__get() RETURNS SETOF VARCHAR AS $$
      WITH _nodes AS (
            SELECT upd_id FROM dbupdates
             WHERE upd_donedat >= current_timestamp - '90 days'::INTERVAL
                OR upd_donedat IS NULL
                OR coalesce(modified_date, insert_date::TIMESTAMP) >= current_timestamp - '90 days'::INTERVAL
                OR upd_sperr
           ),
           _parents AS (
            WITH RECURSIVE _tree AS (
                  SELECT a.upd_id, a.upd_parent, a.upd_bez, a.upd_donedat, a.modified_date, a.insert_date, a.upd_sperr, 0 AS depth FROM dbupdates a WHERE upd_id IN (SELECT upd_id FROM _nodes)
                  UNION
                  SELECT a.upd_id, a.upd_parent, a.upd_bez, a.upd_donedat, a.modified_date, a.insert_date, a.upd_sperr, depth - 1 FROM dbupdates a JOIN _tree p ON a.upd_id = p.upd_parent
            )
            SELECT upd_id FROM _tree
             WHERE depth < 0
             ORDER BY depth
           )
           SELECT * FROM _nodes
           UNION
           SELECT * FROM _parents
  $$ LANGUAGE SQL;
	
    EXCEPTION
		WHEN others THEN
       		RAISE WARNING '%', SQLERRM;
	END; 

	BEGIN

CREATE FUNCTION tsystem.syncro__dbupdates(IN _dblink VARCHAR DEFAULT 'host=pg.prodat-erp.de port=5432 dbname=PRODAT-18.08 user=syncro password=syncro') RETURNS void AS $$
  BEGIN
    SET LOCAL SESSION AUTHORIZATION syncro;

    -- NotThisFields: dbrid,upd_donedat
    INSERT INTO dbupdates ( upd_id,
                            upd_parent,
                            upd_minver,
                            upd_kunde,
                            --upd_donedat,
                            upd_bez,
                            upd_txt,
                            upd_sql,
                            upd_projekt,
                            upd_noerr,
                            upd_sperr,
                            --
                            insert_date,
                            insert_by,
                            modified_by,
                            modified_date
                          )

    SELECT *
      FROM dblink(_dblink,
                   'SELECT upd_id,
                           upd_parent,
                           upd_minver,
                           upd_kunde,
                           --upd_donedat,
                           upd_bez,
                           upd_txt,
                           upd_sql,
                           upd_projekt,
                           upd_noerr,
                           upd_sperr,
                           --
                           insert_date,
                           insert_by,
                           modified_by,
                           modified_date
                      FROM dbupdates
                     WHERE
                              (upd_kunde IS NULL OR upd_kunde LIKE ' || TSystem.quote_literal__connstr_param( TSystem.Settings__Get( 'KUNDE' ) ) || ')
                          AND upd_minver <= ' || TSystem.quote_literal__connstr_param( TSystem.Settings__Get( 'ProdatVersion' ) ) || '
                  ')
        AS ( upd_id_       varchar(30),
             upd_parent    varchar(30),              -- parent (Baumstruktur)
             upd_minver    varchar(11),              -- [SYNCRO:Version] Mindest-Programmversion
             upd_kunde     varchar(20),              -- [SYNCRO:Kunde]
             --upd_donedat timestamp(0),             -- [SYNCRO:NotThisFields] update eingespielt am
             upd_bez       varchar(150),
             upd_txt       text,
             upd_sql       text,
             upd_projekt   varchar(100),
             upd_noerr     boolean,
             upd_sperr     boolean,
             -- System (tables__generate_missing_fields)
             insert_date   date,
             insert_by     varchar(32),
             modified_by   varchar(32),
             modified_date timestamp(0)               -- [SYNCRO:Modified]
           )
     WHERE NOT EXISTS( SELECT true FROM dbupdates WHERE upd_id = upd_id_ );
    ---

    RAISE NOTICE 'dbupdates %', (SELECT count(*) FROM dbupdates);
  END $$ LANGUAGE plpgsql;
	
    EXCEPTION
		WHEN others THEN
       		RAISE WARNING '%', SQLERRM;
	END; 

	BEGIN

PERFORM tsystem.syncro__dbupdates();
	
    EXCEPTION
		WHEN others THEN
       		RAISE WARNING '%', SQLERRM;
	END; 


END $do$ LANGUAGE plpgsql;