CREATE OR REPLACE PROCEDURE prc_load_struct_pas2(IN pschema character varying)
 LANGUAGE plpgsql
AS $procedure$
DECLARE 
  vRec RECORD;
  sSql TEXT;
BEGIN
  CREATE TEMPORARY TABLE if not EXISTS wtmp_object_ddl (obj_name VARCHAR(100), obj_text TEXT, ddl_error NUMERIC(1)) ON COMMIT PRESERVE ROWS;

  IF pschema = 'winmadmin'  THEN -- pregatire instal
    TRUNCATE wtmp_object_ddl;
    sSql := 'INSERT INTO wtmp_object_ddl 
             SELECT obj_name, ''CREATE OR REPLACE PROCEDURE winmadmin.''||Trim(Substr(obj_text, 28)), 0 
             FROM '||pschema||'.OBJECTS_DDL 
             WHERE obj_type = ''PROCEDURE'' AND obj_name = ''prc_instal'''; 
    EXECUTE sSql;
    
    FOR vRec IN (
      SELECT obj_name, obj_text 
      FROM wtmp_object_ddl 
    ) LOOP
      BEGIN 
        EXECUTE vRec.obj_text;
      EXCEPTION
        WHEN OTHERS THEN 
          UPDATE wtmp_object_ddl SET ddl_error = 1 WHERE obj_name = vRec.obj_name;
      END;
    END LOOP;
  END IF;

  TRUNCATE wtmp_object_ddl;
  sSql := 'INSERT INTO wtmp_object_ddl
           SELECT obj_name, ''CREATE TABLE '||pschema||'.''||Trim(Substr(obj_text, 13)), 0 
           FROM '||pschema||'.OBJECTS_DDL 
           WHERE obj_type = ''TABLE'''; 
  EXECUTE sSql;

  FOR vRec IN (
    SELECT obj_name, obj_text 
    FROM wtmp_object_ddl 
  ) LOOP
    BEGIN 
      EXECUTE vRec.obj_text;
    EXCEPTION
      WHEN OTHERS THEN 
        UPDATE wtmp_object_ddl SET ddl_error = 1 WHERE obj_name = vRec.obj_name;
    END;
  END LOOP;

  COMMIT;
end;
$procedure$
