src-db/database/model/postscript-PostgreSql.sql
author Asier Lostalé <asier.lostale@openbravo.com>
Mon, 05 May 2008 06:59:24 +0000
changeset 799 fef2c5e2feb7
parent 756 ae11e4610537
child 1605 8a0fe0193bef
permissions -rw-r--r--
Merged cleanup branch (r3931) with trunk
carlos@0
     1
carlos@0
     2
CREATE OR REPLACE FUNCTION ad_script_disable_triggers(p_seqNoStart numeric)
carlos@0
     3
  RETURNS numeric AS
carlos@0
     4
$BODY$ DECLARE
asier@799
     5
/*************************************************************************
asier@799
     6
* The contents of this file are subject to the Openbravo  Public  License
asier@799
     7
* Version  1.0  (the  "License"),  being   the  Mozilla   Public  License
asier@799
     8
* Version 1.1  with a permitted attribution clause; you may not  use this
asier@799
     9
* file except in compliance with the License. You  may  obtain  a copy of
asier@799
    10
* the License at http://www.openbravo.com/legal/license.html
asier@799
    11
* Software distributed under the License  is  distributed  on  an "AS IS"
asier@799
    12
* basis, WITHOUT WARRANTY OF ANY KIND, either express or implied. See the
asier@799
    13
* License for the specific  language  governing  rights  and  limitations
asier@799
    14
* under the License.
asier@799
    15
* The Original Code is Openbravo ERP.
asier@799
    16
* The Initial Developer of the Original Code is Openbravo SL
asier@799
    17
* All portions are Copyright (C) 2001-2008 Openbravo SL
asier@799
    18
* All Rights Reserved.
asier@799
    19
* Contributor(s):  ______________________________________.
asier@799
    20
************************************************************************/
carlos@0
    21
 v_seqNo      NUMERIC := p_seqNoStart;
carlos@0
    22
 Cur_Triggers RECORD;
carlos@0
    23
BEGIN
carlos@0
    24
 FOR Cur_Triggers IN (SELECT OBJECT_NAME AS NAME, TABLE_NAME
carlos@0
    25
                      FROM USER_OBJECTS
carlos@0
    26
                      WHERE OBJECT_TYPE = 'TRIGGER'
carlos@0
    27
                      ORDER BY OBJECT_NAME) LOOP
carlos@0
    28
    v_seqNo := v_seqNo + 1;
carlos@0
    29
  --      INSERT INTO AD_SCRIPT_SQL VALUES (v_seqNo, 'ALTER TRIGGER '||Cur_Triggers.NAME||' DISABLE');
carlos@0
    30
    INSERT INTO AD_SCRIPT_SQL VALUES (v_seqNo, 'ALTER TABLE  '||Cur_Triggers.TABLE_NAME||' DISABLE TRIGGER '||Cur_Triggers.NAME);
carlos@0
    31
 END LOOP;
carlos@0
    32
 RETURN v_seqNo;
carlos@0
    33
END;   $BODY$
carlos@0
    34
  LANGUAGE 'plpgsql' VOLATILE
carlos@0
    35
/-- END
carlos@0
    36
carlos@0
    37
CREATE OR REPLACE FUNCTION ad_script_disable_constraints(p_seqNoStart numeric)
carlos@0
    38
  RETURNS numeric AS
carlos@0
    39
$BODY$ DECLARE
asier@799
    40
/*************************************************************************
asier@799
    41
* The contents of this file are subject to the Openbravo  Public  License
asier@799
    42
* Version  1.0  (the  "License"),  being   the  Mozilla   Public  License
asier@799
    43
* Version 1.1  with a permitted attribution clause; you may not  use this
asier@799
    44
* file except in compliance with the License. You  may  obtain  a copy of
asier@799
    45
* the License at http://www.openbravo.com/legal/license.html
asier@799
    46
* Software distributed under the License  is  distributed  on  an "AS IS"
asier@799
    47
* basis, WITHOUT WARRANTY OF ANY KIND, either express or implied. See the
asier@799
    48
* License for the specific  language  governing  rights  and  limitations
asier@799
    49
* under the License.
asier@799
    50
* The Original Code is Openbravo ERP.
asier@799
    51
* The Initial Developer of the Original Code is Openbravo SL
asier@799
    52
* All portions are Copyright (C) 2001-2008 Openbravo SL
asier@799
    53
* All Rights Reserved.
asier@799
    54
* Contributor(s):  ______________________________________.
asier@799
    55
************************************************************************/
carlos@0
    56
 v_seqNo      NUMERIC := p_seqNoStart;
carlos@0
    57
 Cur_Constraints RECORD;
carlos@0
    58
BEGIN
carlos@496
    59
 INSERT INTO AD_SCRIPT_SQL VALUES (v_seqNo, 'update pg_class set reltriggers = 0 WHERE PG_CLASS.RELNAMESPACE IN (SELECT PG_NAMESPACE.OID FROM PG_NAMESPACE WHERE PG_NAMESPACE.NSPNAME = CURRENT_SCHEMA());');
carlos@496
    60
 RETURN v_seqNo+1;
carlos@0
    61
END;   $BODY$
carlos@0
    62
  LANGUAGE 'plpgsql' VOLATILE
carlos@0
    63
/-- END
carlos@0
    64
carlos@0
    65
carlos@0
    66
CREATE OR REPLACE FUNCTION ad_script_enable_triggers(p_seqNoStart numeric)
carlos@0
    67
  RETURNS numeric AS
carlos@0
    68
$BODY$ DECLARE
asier@799
    69
/*************************************************************************
asier@799
    70
* The contents of this file are subject to the Openbravo  Public  License
asier@799
    71
* Version  1.0  (the  "License"),  being   the  Mozilla   Public  License
asier@799
    72
* Version 1.1  with a permitted attribution clause; you may not  use this
asier@799
    73
* file except in compliance with the License. You  may  obtain  a copy of
asier@799
    74
* the License at http://www.openbravo.com/legal/license.html
asier@799
    75
* Software distributed under the License  is  distributed  on  an "AS IS"
asier@799
    76
* basis, WITHOUT WARRANTY OF ANY KIND, either express or implied. See the
asier@799
    77
* License for the specific  language  governing  rights  and  limitations
asier@799
    78
* under the License.
asier@799
    79
* The Original Code is Openbravo ERP.
asier@799
    80
* The Initial Developer of the Original Code is Openbravo SL
asier@799
    81
* All portions are Copyright (C) 2001-2008 Openbravo SL
asier@799
    82
* All Rights Reserved.
asier@799
    83
* Contributor(s):  ______________________________________.
asier@799
    84
************************************************************************/
carlos@0
    85
 v_seqNo      NUMERIC := p_seqNoStart;
carlos@0
    86
 Cur_Triggers RECORD;
carlos@0
    87
BEGIN
carlos@0
    88
 FOR Cur_Triggers IN (SELECT OBJECT_NAME AS NAME, TABLE_NAME
carlos@0
    89
                      FROM USER_OBJECTS
carlos@0
    90
                      WHERE OBJECT_TYPE = 'TRIGGER'
carlos@0
    91
                      ORDER BY OBJECT_NAME) LOOP
carlos@0
    92
      v_seqNo := v_seqNo + 1;
carlos@0
    93
--    INSERT INTO AD_SCRIPT_SQL VALUES (v_seqNo, 'ALTER TRIGGER '||Cur_Triggers.NAME||' ENABLE');
carlos@0
    94
    INSERT INTO AD_SCRIPT_SQL VALUES (v_seqNo+100000, 'ALTER TABLE  '||Cur_Triggers.TABLE_NAME||' ENABLE TRIGGER '||Cur_Triggers.NAME);
carlos@0
    95
 END LOOP;
carlos@0
    96
 RETURN v_seqNo;
carlos@0
    97
END;   $BODY$
carlos@0
    98
  LANGUAGE 'plpgsql' VOLATILE
carlos@0
    99
/-- END
carlos@0
   100
carlos@0
   101
CREATE OR REPLACE FUNCTION ad_script_enable_constraints(p_seqNoStart numeric)
carlos@0
   102
  RETURNS numeric AS
carlos@0
   103
$BODY$ DECLARE
asier@799
   104
/*************************************************************************
asier@799
   105
* The contents of this file are subject to the Openbravo  Public  License
asier@799
   106
* Version  1.0  (the  "License"),  being   the  Mozilla   Public  License
asier@799
   107
* Version 1.1  with a permitted attribution clause; you may not  use this
asier@799
   108
* file except in compliance with the License. You  may  obtain  a copy of
asier@799
   109
* the License at http://www.openbravo.com/legal/license.html
asier@799
   110
* Software distributed under the License  is  distributed  on  an "AS IS"
asier@799
   111
* basis, WITHOUT WARRANTY OF ANY KIND, either express or implied. See the
asier@799
   112
* License for the specific  language  governing  rights  and  limitations
asier@799
   113
* under the License.
asier@799
   114
* The Original Code is Openbravo ERP.
asier@799
   115
* The Initial Developer of the Original Code is Openbravo SL
asier@799
   116
* All portions are Copyright (C) 2001-2008 Openbravo SL
asier@799
   117
* All Rights Reserved.
asier@799
   118
* Contributor(s):  ______________________________________.
asier@799
   119
************************************************************************/
carlos@0
   120
 v_seqNo      NUMERIC := p_seqNoStart;
carlos@0
   121
BEGIN
carlos@496
   122
  INSERT INTO AD_SCRIPT_SQL VALUES (v_seqNo + 100000, 'update pg_class set reltriggers = (SELECT count(*) from pg_trigger where pg_class.oid=tgrelid) WHERE PG_CLASS.RELNAMESPACE IN (SELECT PG_NAMESPACE.OID FROM PG_NAMESPACE WHERE PG_NAMESPACE.NSPNAME = CURRENT_SCHEMA());');
carlos@496
   123
  RETURN v_seqNo+100001;
carlos@0
   124
END;   $BODY$
carlos@0
   125
  LANGUAGE 'plpgsql' VOLATILE
carlos@0
   126
/-- END
carlos@0
   127
carlos@496
   128
CREATE OR REPLACE FUNCTION ad_script_drop_recreate_index(p_seqNoStart numeric)
carlos@0
   129
  RETURNS numeric AS
carlos@0
   130
$BODY$ DECLARE
asier@799
   131
/*************************************************************************
asier@799
   132
* The contents of this file are subject to the Openbravo  Public  License
asier@799
   133
* Version  1.0  (the  "License"),  being   the  Mozilla   Public  License
asier@799
   134
* Version 1.1  with a permitted attribution clause; you may not  use this
asier@799
   135
* file except in compliance with the License. You  may  obtain  a copy of
asier@799
   136
* the License at http://www.openbravo.com/legal/license.html
asier@799
   137
* Software distributed under the License  is  distributed  on  an "AS IS"
asier@799
   138
* basis, WITHOUT WARRANTY OF ANY KIND, either express or implied. See the
asier@799
   139
* License for the specific  language  governing  rights  and  limitations
asier@799
   140
* under the License.
asier@799
   141
* The Original Code is Openbravo ERP.
asier@799
   142
* The Initial Developer of the Original Code is Openbravo SL
asier@799
   143
* All portions are Copyright (C) 2001-2008 Openbravo SL
asier@799
   144
* All Rights Reserved.
asier@799
   145
* Contributor(s):  ______________________________________.
asier@799
   146
************************************************************************/
carlos@0
   147
 v_seqNo      NUMERIC := p_seqNoStart;
carlos@0
   148
 v_strTemp VARCHAR(4000):='';
carlos@0
   149
 v_strSql VARCHAR(4000):='';
carlos@0
   150
carlos@0
   151
 Cur_UniqueIndex RECORD;
carlos@0
   152
 Cur_IndexColumns RECORD;
carlos@0
   153
BEGIN
carlos@0
   154
    FOR Cur_UniqueIndex IN (SELECT i.INDEX_NAME, i.TABLE_NAME, i.TABLESPACE_NAME, CONSTRAINT_TYPE
carlos@0
   155
                 FROM USER_INDEXES I left join USER_CONSTRAINTS C1 on c1.INDEX_NAME=I.INDEX_NAME
carlos@0
   156
                 WHERE UNIQUENESS='UNIQUE' AND INDEX_TYPE='NORMAL' AND TABLE_TYPE='TABLE'
carlos@0
   157
               --AND CONSTRAINT_TYPE != 'U'
carlos@0
   158
               ORDER BY INDEX_NAME)
carlos@0
   159
carlos@0
   160
    LOOP
carlos@0
   161
      v_seqNo:=v_seqNo + 1;
carlos@0
   162
      INSERT INTO AD_SCRIPT_SQL VALUES (v_seqNo, 'DROP INDEX '||Cur_UniqueIndex.INDEX_NAME) ;
carlos@0
   163
carlos@0
   164
      IF Cur_UniqueIndex.CONSTRAINT_TYPE != 'P' THEN
carlos@0
   165
           v_strSql:='CREATE INDEX '||Cur_UniqueIndex.INDEX_NAME||' ON '||Cur_UniqueIndex.TABLE_NAME||'(';
carlos@0
   166
           v_strTemp:='';
carlos@0
   167
           FOR Cur_IndexColumns IN
carlos@0
   168
             (SELECT COLUMN_NAME
carlos@0
   169
           FROM USER_IND_COLUMNS
carlos@0
   170
           WHERE INDEX_NAME=Cur_UniqueIndex.INDEX_NAME
carlos@0
   171
           ORDER BY COLUMN_POSITION)
carlos@0
   172
           LOOP
carlos@0
   173
             v_strTemp:=v_strTemp ||','|| Cur_IndexColumns.COLUMN_NAME;
carlos@0
   174
           END LOOP;
carlos@0
   175
           v_strSql:=v_strSql || SUBSTR(v_strTemp, 2, 4000) || ') ';
carlos@0
   176
           INSERT INTO AD_SCRIPT_SQL VALUES(v_seqNo+150000, v_strSql) ;
carlos@0
   177
       END IF;
carlos@0
   178
    END LOOP;
carlos@0
   179
 RETURN v_seqNo;
carlos@0
   180
END;   $BODY$
carlos@0
   181
  LANGUAGE 'plpgsql' VOLATILE
carlos@0
   182
/-- END
carlos@0
   183
carlos@496
   184
CREATE OR REPLACE FUNCTION ad_script_execute(param_Message VARCHAR)
carlos@0
   185
  RETURNS varchar AS
carlos@0
   186
$BODY$ DECLARE
asier@799
   187
/*************************************************************************
asier@799
   188
* The contents of this file are subject to the Openbravo  Public  License
asier@799
   189
* Version  1.0  (the  "License"),  being   the  Mozilla   Public  License
asier@799
   190
* Version 1.1  with a permitted attribution clause; you may not  use this
asier@799
   191
* file except in compliance with the License. You  may  obtain  a copy of
asier@799
   192
* the License at http://www.openbravo.com/legal/license.html
asier@799
   193
* Software distributed under the License  is  distributed  on  an "AS IS"
asier@799
   194
* basis, WITHOUT WARRANTY OF ANY KIND, either express or implied. See the
asier@799
   195
* License for the specific  language  governing  rights  and  limitations
asier@799
   196
* under the License.
asier@799
   197
* The Original Code is Openbravo ERP.
asier@799
   198
* The Initial Developer of the Original Code is Openbravo SL
asier@799
   199
* All portions are Copyright (C) 2001-2008 Openbravo SL
asier@799
   200
* All Rights Reserved.
asier@799
   201
* Contributor(s):  ______________________________________.
asier@799
   202
************************************************************************/
carlos@496
   203
 v_Message       VARCHAR(4000) := '';
carlos@0
   204
 v_ResultStr     VARCHAR(2000) := '';
carlos@0
   205
 Cur_Script RECORD;
carlos@0
   206
BEGIN
carlos@496
   207
 v_Message := param_Message;
carlos@0
   208
 FOR Cur_Script IN (SELECT STRSQL FROM AD_SCRIPT_SQL ORDER BY SEQNO) LOOP
carlos@0
   209
  BEGIN
carlos@0
   210
    RAISE NOTICE '%', Cur_Script.STRSQL;
carlos@0
   211
    EXECUTE(Cur_Script.STRSQL);
carlos@0
   212
  EXCEPTION
carlos@0
   213
     WHEN OTHERS THEN
carlos@0
   214
        IF (LENGTH(v_ResultStr || ': ' || SQLERRM || ' - ' ) < 1980) THEN
carlos@0
   215
          v_ResultStr := v_ResultStr || ': ' || SQLERRM || ' - ';
carlos@0
   216
        END IF;
carlos@0
   217
       RAISE NOTICE '%',SQLERRM;
carlos@0
   218
  END;
carlos@0
   219
 END LOOP;
carlos@0
   220
 IF( LENGTH(v_ResultStr) > 0 ) THEN
carlos@0
   221
    RAISE NOTICE '%', 'Script errors: ' || v_ResultStr;
carlos@0
   222
 END IF;
carlos@496
   223
 return substr(coalesce(v_ResultStr,'') || coalesce(v_Message,''), 1, 2000);
carlos@0
   224
END;   $BODY$
carlos@0
   225
  LANGUAGE 'plpgsql' VOLATILE
carlos@0
   226
/-- END
carlos@0
   227
carlos@0
   228
carlos@0
   229
carlos@0
   230
carlos@0
   231