summaryrefslogtreecommitdiff
path: root/utils/fixme/fixme.pgsql
blob: 7ee23b4d36dc606585700980ec26c1fda5fda76f (plain)
  1. begin;
  2. -- handle NULL chart_id's
  3. insert into chart (accno, description, charttype) values ('1000000000', 'Broken SQL-Ledger Data', 'L');
  4. --handle duplicate transaction id's.
  5. CREATE TEMPORARY TABLE id_agregator (id INT, table_name text);
  6. insert into id_agregator (id, table_name) SELECT id, 'ar' from ar;
  7. insert into id_agregator (id, table_name) SELECT id, 'ap' from ap;
  8. insert into id_agregator (id, table_name) SELECT id, 'business' from business;
  9. insert into id_agregator (id, table_name) SELECT id, 'chart' from chart;
  10. insert into id_agregator (id, table_name) SELECT id, 'customer' from customer;
  11. insert into id_agregator (id, table_name)
  12. SELECT id, 'department' from department;
  13. insert into id_agregator (id, table_name) SELECT id, 'employee' from employee;
  14. insert into id_agregator (id, table_name) SELECT id, 'gl' from gl;
  15. insert into id_agregator (id, table_name) SELECT id, 'oe' from oe;
  16. insert into id_agregator (id, table_name) SELECT id, 'parts' from parts;
  17. insert into id_agregator (id, table_name)
  18. SELECT id, 'partsgroup' from partsgroup;
  19. insert into id_agregator (id, table_name) SELECT id, 'project' from project;
  20. insert into id_agregator (id, table_name) SELECT id, 'vendor' from vendor;
  21. insert into id_agregator (id, table_name) SELECT id, 'warehouse' from warehouse;
  22. CREATE TEMPORARY VIEW id_view1 AS
  23. SELECT id, count(*) AS num_rows FROM id_agregator
  24. GROUP BY id HAVING num_rows > 1;
  25. select setval('id', select max(id) + 1 from id_agregator);
  26. create function fix_dupes() RETURNS OPAQUE AS
  27. '
  28. DECLARE
  29. dupe_id id_agregator%ROWTYPE;
  30. BEGIN
  31. FOR dupe_id IN SELECT id FROM id_agregator
  32. WHERE id IN (SELECT id FROM id_view1)
  33. LOOP
  34. EXECUTE ''UPDATE '' || dupe_id.table_name ||
  35. SET id = nextval(''''id'''') WHERE
  36. id = '' ||dupe_id.id;
  37. UPDATE acc_trans SET trans_id = currval(''id'') WHERE
  38. id = '' ||dupe_id.id;
  39. INSERT INTO acc_trans (trans_id, amount, chart_id) VALUES (
  40. currval(''id''), '1', (
  41. SELECT id FROM chart WHERE accno = ''1000000000''
  42. )
  43. );
  44. INSERT INTO acc_trans (trans_id, amount, chart_id) VALUES (
  45. currval(''id''), '-1', (
  46. SELECT id FROM chart WHERE accno = ''1000000000''
  47. )
  48. );
  49. END;
  50. ' LANGUAGE PLPGSQL;
  51. SELECT fix_dupes ();
  52. drop function fix_dupes();
  53. commit;