Merged Pg-tables, functions, and indeces into Pg-database.sql

git-svn-id: https://ledger-smb.svn.sourceforge.net/svnroot/ledger-smb/trunk@449 4979c152-3d1c-0410-bac9-87ea11338e46

5 files changed, 422 insertions, 428 deletions

diff --git a/Changelog b/Changelog
index a429bf76..f24b9eac 100644
--- a/Changelog
+++ b/Changelog
@@ -5,6 +5,7 @@ Database:
 * Added defined primary keys to all tables (Chris T)
 * Database upgrades now use psql (Chris T)
 * Defaults table now uses a simple key->value system (Chris T)
+* Merged Pg-tables, Pg-functions, and Pg-indeces into Pg-database (Chris T)
 
 Security:
 * Added whitelist of allowed directories to file editor (Seneca)
diff --git a/LedgerSMB/User.pm b/LedgerSMB/User.pm
index a16cf7d5..67249a04 100755
--- a/LedgerSMB/User.pm
+++ b/LedgerSMB/User.pm
@@ -367,13 +367,9 @@ sub dbcreate {
 		? 'Pg' 
 		: $form->{dbdriver};
   
-	my $filename = qq|sql/${dbdriver}-tables.sql|;
+	my $filename = qq|sql/Pg-database.sql|;
 	$self->process_query($form, $dbh, $filename);
   
-	# create functions
-	$filename = qq|sql/${dbdriver}-functions.sql|;
-	$self->process_query($form, $dbh, $filename);
-
 	# load gifi
 	($filename) = split /_/, $form->{chart};
 	$filename =~ s/_//;
@@ -383,9 +379,6 @@ sub dbcreate {
 	$filename = qq|sql/$form->{chart}-chart.sql|;
 	$self->process_query($form, $dbh, $filename);
 
-	# create indices
-	$filename = qq|sql/${dbdriver}-indices.sql|;
-	$self->process_query($form, $dbh, $filename);
  
 	# create custom tables and functions
 	my $item;
diff --git a/sql/Pg-tables.sql b/sql/Pg-database.sql
index 92dfbf26..e3e96552 100755..100644
--- a/sql/Pg-tables.sql
+++ b/sql/Pg-database.sql
@@ -778,3 +778,423 @@ last_used TIMESTAMP default now(),
 users_id INTEGER  -- NOT NULL references users(id)
 );
 
+create index acc_trans_trans_id_key on acc_trans (trans_id);
+create index acc_trans_chart_id_key on acc_trans (chart_id);
+create index acc_trans_transdate_key on acc_trans (transdate);
+create index acc_trans_source_key on acc_trans (lower(source));
+--
+create index ap_id_key on ap (id);
+create index ap_transdate_key on ap (transdate);
+create index ap_invnumber_key on ap (invnumber);
+create index ap_ordnumber_key on ap (ordnumber);
+create index ap_vendor_id_key on ap (vendor_id);
+create index ap_employee_id_key on ap (employee_id);
+create index ap_quonumber_key on ap (quonumber);
+--
+create index ar_id_key on ar (id);
+create index ar_transdate_key on ar (transdate);
+create index ar_invnumber_key on ar (invnumber);
+create index ar_ordnumber_key on ar (ordnumber);
+create index ar_customer_id_key on ar (customer_id);
+create index ar_employee_id_key on ar (employee_id);
+create index ar_quonumber_key on ar (quonumber);
+--
+create index assembly_id_key on assembly (id);
+--
+create index chart_id_key on chart (id);
+create unique index chart_accno_key on chart (accno);
+create index chart_category_key on chart (category);
+create index chart_link_key on chart (link);
+create index chart_gifi_accno_key on chart (gifi_accno);
+--
+create index customer_id_key on customer (id);
+create index customer_customernumber_key on customer (customernumber);
+create index customer_name_key on customer (lower(name));
+create index customer_contact_key on customer (lower(contact));
+create index customer_customer_id_key on customertax (customer_id);
+--
+create index employee_id_key on employee (id);
+create unique index employee_login_key on employee (login);
+create index employee_name_key on employee (lower(name));
+--
+create index exchangerate_ct_key on exchangerate (curr, transdate);
+--
+create unique index gifi_accno_key on gifi (accno);
+--
+create index gl_id_key on gl (id);
+create index gl_transdate_key on gl (transdate);
+create index gl_reference_key on gl (reference);
+create index gl_description_key on gl (lower(description));
+create index gl_employee_id_key on gl (employee_id);
+--
+create index invoice_id_key on invoice (id);
+create index invoice_trans_id_key on invoice (trans_id);
+--
+create index makemodel_parts_id_key on makemodel (parts_id);
+create index makemodel_make_key on makemodel (lower(make));
+create index makemodel_model_key on makemodel (lower(model));
+--
+create index oe_id_key on oe (id);
+create index oe_transdate_key on oe (transdate);
+create index oe_ordnumber_key on oe (ordnumber);
+create index oe_employee_id_key on oe (employee_id);
+create index orderitems_trans_id_key on orderitems (trans_id);
+create index orderitems_id_key on orderitems (id);
+--
+create index parts_id_key on parts (id);
+create index parts_partnumber_key on parts (lower(partnumber));
+create index parts_description_key on parts (lower(description));
+create index partstax_parts_id_key on partstax (parts_id);
+--
+create index vendor_id_key on vendor (id);
+create index vendor_name_key on vendor (lower(name));
+create index vendor_vendornumber_key on vendor (vendornumber);
+create index vendor_contact_key on vendor (lower(contact));
+create index vendortax_vendor_id_key on vendortax (vendor_id);
+--
+create index shipto_trans_id_key on shipto (trans_id);
+--
+create index project_id_key on project (id);
+create unique index projectnumber_key on project (projectnumber);
+--
+create index partsgroup_id_key on partsgroup (id);
+create unique index partsgroup_key on partsgroup (partsgroup);
+--
+create index status_trans_id_key on status (trans_id);
+--
+create index department_id_key on department (id);
+--
+create index partsvendor_vendor_id_key on partsvendor (vendor_id);
+create index partsvendor_parts_id_key on partsvendor (parts_id);
+--
+create index pricegroup_pricegroup_key on pricegroup (pricegroup);
+create index pricegroup_id_key on pricegroup (id);
+--
+create index audittrail_trans_id_key on audittrail (trans_id);
+--
+create index translation_trans_id_key on translation (trans_id);
+--
+create unique index language_code_key on language (code);
+--
+create index jcitems_id_key on jcitems (id);
+
+CREATE LANGUAGE plpgsql;
+--
+CREATE FUNCTION del_yearend() RETURNS OPAQUE AS '
+begin
+  delete from yearend where trans_id = old.id;
+  return NULL;
+end;
+' language 'plpgsql';
+-- end function
+--
+CREATE TRIGGER del_yearend AFTER DELETE ON gl FOR EACH ROW EXECUTE PROCEDURE del_yearend();
+-- end trigger
+--
+CREATE FUNCTION del_department() RETURNS OPAQUE AS '
+begin
+  delete from dpt_trans where trans_id = old.id;
+  return NULL;
+end;
+' language 'plpgsql';
+-- end function
+--
+CREATE TRIGGER del_department AFTER DELETE ON ar FOR EACH ROW EXECUTE PROCEDURE del_department();
+-- end trigger
+CREATE TRIGGER del_department AFTER DELETE ON ap FOR EACH ROW EXECUTE PROCEDURE del_department();
+-- end trigger
+CREATE TRIGGER del_department AFTER DELETE ON gl FOR EACH ROW EXECUTE PROCEDURE del_department();
+-- end trigger
+CREATE TRIGGER del_department AFTER DELETE ON oe FOR EACH ROW EXECUTE PROCEDURE del_department();
+-- end trigger
+--
+CREATE FUNCTION del_customer() RETURNS OPAQUE AS '
+begin
+  delete from shipto where trans_id = old.id;
+  delete from customertax where customer_id = old.id;
+  delete from partscustomer where customer_id = old.id;
+  return NULL;
+end;
+' language 'plpgsql';
+-- end function
+--
+CREATE TRIGGER del_customer AFTER DELETE ON customer FOR EACH ROW EXECUTE PROCEDURE del_customer();
+-- end trigger
+--
+CREATE FUNCTION del_vendor() RETURNS OPAQUE AS '
+begin
+  delete from shipto where trans_id = old.id;
+  delete from vendortax where vendor_id = old.id;
+  delete from partsvendor where vendor_id = old.id;
+  return NULL;
+end;
+' language 'plpgsql';
+-- end function
+--
+CREATE TRIGGER del_vendor AFTER DELETE ON vendor FOR EACH ROW EXECUTE PROCEDURE del_vendor();
+-- end trigger
+--
+CREATE FUNCTION del_exchangerate() RETURNS OPAQUE AS '
+
+declare
+  t_transdate date;
+  t_curr char(3);
+  t_id int;
+  d_curr text;
+
+begin
+
+  select into d_curr substr(curr,1,3) from defaults;
+  
+  if TG_RELNAME = ''ar'' then
+    select into t_curr, t_transdate curr, transdate from ar where id = old.id;
+  end if;
+  if TG_RELNAME = ''ap'' then
+    select into t_curr, t_transdate curr, transdate from ap where id = old.id;
+  end if;
+  if TG_RELNAME = ''oe'' then
+    select into t_curr, t_transdate curr, transdate from oe where id = old.id;
+  end if;
+
+  if d_curr != t_curr then
+
+    select into t_id a.id from acc_trans ac
+    join ar a on (a.id = ac.trans_id)
+    where a.curr = t_curr
+    and ac.transdate = t_transdate
+
+    except select a.id from ar a where a.id = old.id
+    
+    union
+    
+    select a.id from acc_trans ac
+    join ap a on (a.id = ac.trans_id)
+    where a.curr = t_curr
+    and ac.transdate = t_transdate
+    
+    except select a.id from ap a where a.id = old.id
+    
+    union
+    
+    select o.id from oe o
+    where o.curr = t_curr
+    and o.transdate = t_transdate
+    
+    except select o.id from oe o where o.id = old.id;
+
+    if not found then
+      delete from exchangerate where curr = t_curr and transdate = t_transdate;
+    end if;
+  end if;
+return old;
+
+end;
+' language 'plpgsql';
+-- end function
+--
+CREATE TRIGGER del_exchangerate BEFORE DELETE ON ar FOR EACH ROW EXECUTE PROCEDURE del_exchangerate();
+-- end trigger
+--
+CREATE TRIGGER del_exchangerate BEFORE DELETE ON ap FOR EACH ROW EXECUTE PROCEDURE del_exchangerate();
+-- end trigger
+--
+CREATE TRIGGER del_exchangerate BEFORE DELETE ON oe FOR EACH ROW EXECUTE PROCEDURE del_exchangerate();
+-- end trigger
+--
+CREATE FUNCTION check_inventory() RETURNS OPAQUE AS '
+
+declare
+  itemid int;
+  row_data inventory%rowtype;
+
+begin
+
+  if not old.quotation then
+    for row_data in select * from inventory where trans_id = old.id loop
+      select into itemid id from orderitems where trans_id = old.id and id = row_data.orderitems_id;
+
+      if itemid is null then
+	delete from inventory where trans_id = old.id and orderitems_id = row_data.orderitems_id;
+      end if;
+    end loop;
+  end if;
+return old;
+end;
+' language 'plpgsql';
+-- end function
+--
+CREATE TRIGGER check_inventory AFTER UPDATE ON oe FOR EACH ROW EXECUTE PROCEDURE check_inventory();
+-- end trigger
+--
+--
+CREATE FUNCTION check_department() RETURNS OPAQUE AS '
+
+declare
+  dpt_id int;
+
+begin
+ 
+  if new.department_id = 0 then
+    delete from dpt_trans where trans_id = new.id;
+    return NULL;
+  end if;
+
+  select into dpt_id trans_id from dpt_trans where trans_id = new.id;
+  
+  if dpt_id > 0 then
+    update dpt_trans set department_id = new.department_id where trans_id = dpt_id;
+  else
+    insert into dpt_trans (trans_id, department_id) values (new.id, new.department_id);
+  end if;
+return NULL;
+
+end;
+' language 'plpgsql';
+-- end function
+--
+CREATE TRIGGER check_department AFTER INSERT OR UPDATE ON ar FOR EACH ROW EXECUTE PROCEDURE check_department();
+-- end trigger
+CREATE TRIGGER check_department AFTER INSERT OR UPDATE ON ap FOR EACH ROW EXECUTE PROCEDURE check_department();
+-- end trigger
+CREATE TRIGGER check_department AFTER INSERT OR UPDATE ON gl FOR EACH ROW EXECUTE PROCEDURE check_department();
+-- end trigger
+CREATE TRIGGER check_department AFTER INSERT OR UPDATE ON oe FOR EACH ROW EXECUTE PROCEDURE check_department();
+-- end trigger
+--
+CREATE FUNCTION del_recurring() returns opaque as '
+BEGIN
+  DELETE FROM recurring WHERE id = old.id;
+  DELETE FROM recurringemail WHERE id = old.id;
+  DELETE FROM recurringprint WHERE id = old.id;
+  RETURN NULL;
+END;
+' language 'plpgsql';
+--end function
+CREATE TRIGGER del_recurring AFTER DELETE ON ar FOR EACH ROW EXECUTE PROCEDURE del_recurring();
+-- end trigger
+CREATE TRIGGER del_recurring AFTER DELETE ON ap FOR EACH ROW EXECUTE PROCEDURE del_recurring();
+-- end trigger
+CREATE TRIGGER del_recurring AFTER DELETE ON gl FOR EACH ROW EXECUTE PROCEDURE del_recurring();
+-- end trigger
+--
+CREATE FUNCTION avgcost(int) RETURNS FLOAT AS '
+
+DECLARE
+
+v_cost float;
+v_qty float;
+v_parts_id alias for $1;
+
+BEGIN
+
+  SELECT INTO v_cost, v_qty SUM(i.sellprice * i.qty), SUM(i.qty)
+  FROM invoice i
+  JOIN ap a ON (a.id = i.trans_id)
+  WHERE i.parts_id = v_parts_id;
+  
+  IF v_cost IS NULL THEN
+    v_cost := 0;
+  END IF;
+
+  IF NOT v_qty IS NULL THEN
+    IF v_qty = 0 THEN
+      v_cost := 0;
+    ELSE
+      v_cost := v_cost/v_qty;
+    END IF;
+  END IF;
+
+RETURN v_cost;
+END;
+' language 'plpgsql';
+-- end function
+--
+CREATE FUNCTION lastcost(int) RETURNS FLOAT AS '
+
+DECLARE
+
+v_cost float;
+v_parts_id alias for $1;
+
+BEGIN
+
+  SELECT INTO v_cost sellprice FROM invoice i
+  JOIN ap a ON (a.id = i.trans_id)
+  WHERE i.parts_id = v_parts_id
+  ORDER BY a.transdate desc, a.id desc
+  LIMIT 1;
+
+  IF v_cost IS NULL THEN
+    v_cost := 0;
+  END IF;
+
+RETURN v_cost;
+END;
+' language plpgsql;
+-- end function
+--
+
+CREATE OR REPLACE FUNCTION trigger_parts_short() RETURNS TRIGGER
+AS
+'
+BEGIN
+  IF NEW.onhand >= NEW.rop THEN
+    NOTIFY parts_short;
+  END IF;
+  RETURN NEW;
+END;
+' LANGUAGE PLPGSQL;
+-- end function
+
+CREATE TRIGGER parts_short AFTER UPDATE ON parts 
+FOR EACH ROW EXECUTE PROCEDURE trigger_parts_short();
+-- end function
+
+CREATE OR REPLACE FUNCTION add_custom_field (VARCHAR, VARCHAR, VARCHAR) 
+RETURNS BOOL AS
+'
+DECLARE
+table_name ALIAS FOR $1;
+new_field_name ALIAS FOR $2;
+field_datatype ALIAS FOR $3;
+
+BEGIN
+	EXECUTE ''SELECT TABLE_ID FROM custom_table_catalog 
+		WHERE extends = '''''' || table_name || '''''' '';
+	IF NOT FOUND THEN
+		BEGIN
+			INSERT INTO custom_table_catalog (extends) 
+				VALUES (table_name);
+			EXECUTE ''CREATE TABLE custom_''||table_name || 
+				'' (row_id INT)'';
+		EXCEPTION WHEN duplicate_table THEN
+			-- do nothing
+		END;
+	END IF;
+	EXECUTE ''INSERT INTO custom_field_catalog (field_name, table_id)
+	VALUES ( '''''' || new_field_name ||'''''', (SELECT table_id FROM custom_table_catalog
+		WHERE extends = ''''''|| table_name || ''''''))'';
+	EXECUTE ''ALTER TABLE custom_''||table_name || '' ADD COLUMN '' 
+		|| new_field_name || '' '' || field_datatype;
+	RETURN TRUE;
+END;
+' LANGUAGE PLPGSQL;
+-- end function
+
+CREATE OR REPLACE FUNCTION drop_custom_field (VARCHAR, VARCHAR) 
+RETURNS BOOL AS
+'
+DECLARE
+table_name ALIAS FOR $1;
+custom_field_name ALIAS FOR $2;
+BEGIN
+	DELETE FROM custom_field_catalog 
+	WHERE field_name = custom_field_name AND 
+		table_id = (SELECT table_id FROM custom_table_catalog 
+			WHERE extends = table_name);
+	EXECUTE ''ALTER TABLE custom_'' || table_name || 
+		'' DROP COLUMN '' || custom_field_name;
+	RETURN TRUE;	
+END;
+' LANGUAGE PLPGSQL;
+-- end function
diff --git a/sql/Pg-functions.sql b/sql/Pg-functions.sql
deleted file mode 100755
index b42e2083..00000000
--- a/sql/Pg-functions.sql
+++ /dev/null
@@ -1,320 +0,0 @@
-CREATE LANGUAGE plpgsql;
---
-CREATE FUNCTION del_yearend() RETURNS OPAQUE AS '
-begin
-  delete from yearend where trans_id = old.id;
-  return NULL;
-end;
-' language 'plpgsql';
--- end function
---
-CREATE TRIGGER del_yearend AFTER DELETE ON gl FOR EACH ROW EXECUTE PROCEDURE del_yearend();
--- end trigger
---
-CREATE FUNCTION del_department() RETURNS OPAQUE AS '
-begin
-  delete from dpt_trans where trans_id = old.id;
-  return NULL;
-end;
-' language 'plpgsql';
--- end function
---
-CREATE TRIGGER del_department AFTER DELETE ON ar FOR EACH ROW EXECUTE PROCEDURE del_department();
--- end trigger
-CREATE TRIGGER del_department AFTER DELETE ON ap FOR EACH ROW EXECUTE PROCEDURE del_department();
--- end trigger
-CREATE TRIGGER del_department AFTER DELETE ON gl FOR EACH ROW EXECUTE PROCEDURE del_department();
--- end trigger
-CREATE TRIGGER del_department AFTER DELETE ON oe FOR EACH ROW EXECUTE PROCEDURE del_department();
--- end trigger
---
-CREATE FUNCTION del_customer() RETURNS OPAQUE AS '
-begin
-  delete from shipto where trans_id = old.id;
-  delete from customertax where customer_id = old.id;
-  delete from partscustomer where customer_id = old.id;
-  return NULL;
-end;
-' language 'plpgsql';
--- end function
---
-CREATE TRIGGER del_customer AFTER DELETE ON customer FOR EACH ROW EXECUTE PROCEDURE del_customer();
--- end trigger
---
-CREATE FUNCTION del_vendor() RETURNS OPAQUE AS '
-begin
-  delete from shipto where trans_id = old.id;
-  delete from vendortax where vendor_id = old.id;
-  delete from partsvendor where vendor_id = old.id;
-  return NULL;
-end;
-' language 'plpgsql';
--- end function
---
-CREATE TRIGGER del_vendor AFTER DELETE ON vendor FOR EACH ROW EXECUTE PROCEDURE del_vendor();
--- end trigger
---
-CREATE FUNCTION del_exchangerate() RETURNS OPAQUE AS '
-
-declare
-  t_transdate date;
-  t_curr char(3);
-  t_id int;
-  d_curr text;
-
-begin
-
-  select into d_curr substr(curr,1,3) from defaults;
-  
-  if TG_RELNAME = ''ar'' then
-    select into t_curr, t_transdate curr, transdate from ar where id = old.id;
-  end if;
-  if TG_RELNAME = ''ap'' then
-    select into t_curr, t_transdate curr, transdate from ap where id = old.id;
-  end if;
-  if TG_RELNAME = ''oe'' then
-    select into t_curr, t_transdate curr, transdate from oe where id = old.id;
-  end if;
-
-  if d_curr != t_curr then
-
-    select into t_id a.id from acc_trans ac
-    join ar a on (a.id = ac.trans_id)
-    where a.curr = t_curr
-    and ac.transdate = t_transdate
-
-    except select a.id from ar a where a.id = old.id
-    
-    union
-    
-    select a.id from acc_trans ac
-    join ap a on (a.id = ac.trans_id)
-    where a.curr = t_curr
-    and ac.transdate = t_transdate
-    
-    except select a.id from ap a where a.id = old.id
-    
-    union
-    
-    select o.id from oe o
-    where o.curr = t_curr
-    and o.transdate = t_transdate
-    
-    except select o.id from oe o where o.id = old.id;
-
-    if not found then
-      delete from exchangerate where curr = t_curr and transdate = t_transdate;
-    end if;
-  end if;
-return old;
-
-end;
-' language 'plpgsql';
--- end function
---
-CREATE TRIGGER del_exchangerate BEFORE DELETE ON ar FOR EACH ROW EXECUTE PROCEDURE del_exchangerate();
--- end trigger
---
-CREATE TRIGGER del_exchangerate BEFORE DELETE ON ap FOR EACH ROW EXECUTE PROCEDURE del_exchangerate();
--- end trigger
---
-CREATE TRIGGER del_exchangerate BEFORE DELETE ON oe FOR EACH ROW EXECUTE PROCEDURE del_exchangerate();
--- end trigger
---
-CREATE FUNCTION check_inventory() RETURNS OPAQUE AS '
-
-declare
-  itemid int;
-  row_data inventory%rowtype;
-
-begin
-
-  if not old.quotation then
-    for row_data in select * from inventory where trans_id = old.id loop
-      select into itemid id from orderitems where trans_id = old.id and id = row_data.orderitems_id;
-
-      if itemid is null then
-	delete from inventory where trans_id = old.id and orderitems_id = row_data.orderitems_id;
-      end if;
-    end loop;
-  end if;
-return old;
-end;
-' language 'plpgsql';
--- end function
---
-CREATE TRIGGER check_inventory AFTER UPDATE ON oe FOR EACH ROW EXECUTE PROCEDURE check_inventory();
--- end trigger
---
---
-CREATE FUNCTION check_department() RETURNS OPAQUE AS '
-
-declare
-  dpt_id int;
-
-begin
- 
-  if new.department_id = 0 then
-    delete from dpt_trans where trans_id = new.id;
-    return NULL;
-  end if;
-
-  select into dpt_id trans_id from dpt_trans where trans_id = new.id;
-  
-  if dpt_id > 0 then
-    update dpt_trans set department_id = new.department_id where trans_id = dpt_id;
-  else
-    insert into dpt_trans (trans_id, department_id) values (new.id, new.department_id);
-  end if;
-return NULL;
-
-end;
-' language 'plpgsql';
--- end function
---
-CREATE TRIGGER check_department AFTER INSERT OR UPDATE ON ar FOR EACH ROW EXECUTE PROCEDURE check_department();
--- end trigger
-CREATE TRIGGER check_department AFTER INSERT OR UPDATE ON ap FOR EACH ROW EXECUTE PROCEDURE check_department();
--- end trigger
-CREATE TRIGGER check_department AFTER INSERT OR UPDATE ON gl FOR EACH ROW EXECUTE PROCEDURE check_department();
--- end trigger
-CREATE TRIGGER check_department AFTER INSERT OR UPDATE ON oe FOR EACH ROW EXECUTE PROCEDURE check_department();
--- end trigger
---
-CREATE FUNCTION del_recurring() returns opaque as '
-BEGIN
-  DELETE FROM recurring WHERE id = old.id;
-  DELETE FROM recurringemail WHERE id = old.id;
-  DELETE FROM recurringprint WHERE id = old.id;
-  RETURN NULL;
-END;
-' language 'plpgsql';
---end function
-CREATE TRIGGER del_recurring AFTER DELETE ON ar FOR EACH ROW EXECUTE PROCEDURE del_recurring();
--- end trigger
-CREATE TRIGGER del_recurring AFTER DELETE ON ap FOR EACH ROW EXECUTE PROCEDURE del_recurring();
--- end trigger
-CREATE TRIGGER del_recurring AFTER DELETE ON gl FOR EACH ROW EXECUTE PROCEDURE del_recurring();
--- end trigger
---
-CREATE FUNCTION avgcost(int) RETURNS FLOAT AS '
-
-DECLARE
-
-v_cost float;
-v_qty float;
-v_parts_id alias for $1;
-
-BEGIN
-
-  SELECT INTO v_cost, v_qty SUM(i.sellprice * i.qty), SUM(i.qty)
-  FROM invoice i
-  JOIN ap a ON (a.id = i.trans_id)
-  WHERE i.parts_id = v_parts_id;
-  
-  IF v_cost IS NULL THEN
-    v_cost := 0;
-  END IF;
-
-  IF NOT v_qty IS NULL THEN
-    IF v_qty = 0 THEN
-      v_cost := 0;
-    ELSE
-      v_cost := v_cost/v_qty;
-    END IF;
-  END IF;
-
-RETURN v_cost;
-END;
-' language 'plpgsql';
--- end function
---
-CREATE FUNCTION lastcost(int) RETURNS FLOAT AS '
-
-DECLARE
-
-v_cost float;
-v_parts_id alias for $1;
-
-BEGIN
-
-  SELECT INTO v_cost sellprice FROM invoice i
-  JOIN ap a ON (a.id = i.trans_id)
-  WHERE i.parts_id = v_parts_id
-  ORDER BY a.transdate desc, a.id desc
-  LIMIT 1;
-
-  IF v_cost IS NULL THEN
-    v_cost := 0;
-  END IF;
-
-RETURN v_cost;
-END;
-' language plpgsql;
--- end function
---
-
-CREATE OR REPLACE FUNCTION trigger_parts_short() RETURNS TRIGGER
-AS
-'
-BEGIN
-  IF NEW.onhand >= NEW.rop THEN
-    NOTIFY parts_short;
-  END IF;
-  RETURN NEW;
-END;
-' LANGUAGE PLPGSQL;
--- end function
-
-CREATE TRIGGER parts_short AFTER UPDATE ON parts 
-FOR EACH ROW EXECUTE PROCEDURE trigger_parts_short();
--- end function
-
-CREATE OR REPLACE FUNCTION add_custom_field (VARCHAR, VARCHAR, VARCHAR) 
-RETURNS BOOL AS
-'
-DECLARE
-table_name ALIAS FOR $1;
-new_field_name ALIAS FOR $2;
-field_datatype ALIAS FOR $3;
-
-BEGIN
-	EXECUTE ''SELECT TABLE_ID FROM custom_table_catalog 
-		WHERE extends = '''''' || table_name || '''''' '';
-	IF NOT FOUND THEN
-		BEGIN
-			INSERT INTO custom_table_catalog (extends) 
-				VALUES (table_name);
-			EXECUTE ''CREATE TABLE custom_''||table_name || 
-				'' (row_id INT)'';
-		EXCEPTION WHEN duplicate_table THEN
-			-- do nothing
-		END;
-	END IF;
-	EXECUTE ''INSERT INTO custom_field_catalog (field_name, table_id)
-	VALUES ( '''''' || new_field_name ||'''''', (SELECT table_id FROM custom_table_catalog
-		WHERE extends = ''''''|| table_name || ''''''))'';
-	EXECUTE ''ALTER TABLE custom_''||table_name || '' ADD COLUMN '' 
-		|| new_field_name || '' '' || field_datatype;
-	RETURN TRUE;
-END;
-' LANGUAGE PLPGSQL;
--- end function
-
-CREATE OR REPLACE FUNCTION drop_custom_field (VARCHAR, VARCHAR) 
-RETURNS BOOL AS
-'
-DECLARE
-table_name ALIAS FOR $1;
-custom_field_name ALIAS FOR $2;
-BEGIN
-	DELETE FROM custom_field_catalog 
-	WHERE field_name = custom_field_name AND 
-		table_id = (SELECT table_id FROM custom_table_catalog 
-			WHERE extends = table_name);
-	EXECUTE ''ALTER TABLE custom_'' || table_name || 
-		'' DROP COLUMN '' || custom_field_name;
-	RETURN TRUE;	
-END;
-' LANGUAGE PLPGSQL;
--- end function
diff --git a/sql/Pg-indices.sql b/sql/Pg-indices.sql
deleted file mode 100755
index 1da593f6..00000000
--- a/sql/Pg-indices.sql
+++ /dev/null
@@ -1,100 +0,0 @@
-create index acc_trans_trans_id_key on acc_trans (trans_id);
-create index acc_trans_chart_id_key on acc_trans (chart_id);
-create index acc_trans_transdate_key on acc_trans (transdate);
-create index acc_trans_source_key on acc_trans (lower(source));
---
-create index ap_id_key on ap (id);
-create index ap_transdate_key on ap (transdate);
-create index ap_invnumber_key on ap (invnumber);
-create index ap_ordnumber_key on ap (ordnumber);
-create index ap_vendor_id_key on ap (vendor_id);
-create index ap_employee_id_key on ap (employee_id);
-create index ap_quonumber_key on ap (quonumber);
---
-create index ar_id_key on ar (id);
-create index ar_transdate_key on ar (transdate);
-create index ar_invnumber_key on ar (invnumber);
-create index ar_ordnumber_key on ar (ordnumber);
-create index ar_customer_id_key on ar (customer_id);
-create index ar_employee_id_key on ar (employee_id);
-create index ar_quonumber_key on ar (quonumber);
---
-create index assembly_id_key on assembly (id);
---
-create index chart_id_key on chart (id);
-create unique index chart_accno_key on chart (accno);
-create index chart_category_key on chart (category);
-create index chart_link_key on chart (link);
-create index chart_gifi_accno_key on chart (gifi_accno);
---
-create index customer_id_key on customer (id);
-create index customer_customernumber_key on customer (customernumber);
-create index customer_name_key on customer (lower(name));
-create index customer_contact_key on customer (lower(contact));
-create index customer_customer_id_key on customertax (customer_id);
---
-create index employee_id_key on employee (id);
-create unique index employee_login_key on employee (login);
-create index employee_name_key on employee (lower(name));
---
-create index exchangerate_ct_key on exchangerate (curr, transdate);
---
-create unique index gifi_accno_key on gifi (accno);
---
-create index gl_id_key on gl (id);
-create index gl_transdate_key on gl (transdate);
-create index gl_reference_key on gl (reference);
-create index gl_description_key on gl (lower(description));
-create index gl_employee_id_key on gl (employee_id);
---
-create index invoice_id_key on invoice (id);
-create index invoice_trans_id_key on invoice (trans_id);
---
-create index makemodel_parts_id_key on makemodel (parts_id);
-create index makemodel_make_key on makemodel (lower(make));
-create index makemodel_model_key on makemodel (lower(model));
---
-create index oe_id_key on oe (id);
-create index oe_transdate_key on oe (transdate);
-create index oe_ordnumber_key on oe (ordnumber);
-create index oe_employee_id_key on oe (employee_id);
-create index orderitems_trans_id_key on orderitems (trans_id);
-create index orderitems_id_key on orderitems (id);
---
-create index parts_id_key on parts (id);
-create index parts_partnumber_key on parts (lower(partnumber));
-create index parts_description_key on parts (lower(description));
-create index partstax_parts_id_key on partstax (parts_id);
---
-create index vendor_id_key on vendor (id);
-create index vendor_name_key on vendor (lower(name));
-create index vendor_vendornumber_key on vendor (vendornumber);
-create index vendor_contact_key on vendor (lower(contact));
-create index vendortax_vendor_id_key on vendortax (vendor_id);
---
-create index shipto_trans_id_key on shipto (trans_id);
---
-create index project_id_key on project (id);
-create unique index projectnumber_key on project (projectnumber);
---
-create index partsgroup_id_key on partsgroup (id);
-create unique index partsgroup_key on partsgroup (partsgroup);
---
-create index status_trans_id_key on status (trans_id);
---
-create index department_id_key on department (id);
---
-create index partsvendor_vendor_id_key on partsvendor (vendor_id);
-create index partsvendor_parts_id_key on partsvendor (parts_id);
---
-create index pricegroup_pricegroup_key on pricegroup (pricegroup);
-create index pricegroup_id_key on pricegroup (id);
---
-create index audittrail_trans_id_key on audittrail (trans_id);
---
-create index translation_trans_id_key on translation (trans_id);
---
-create unique index language_code_key on language (code);
---
-create index jcitems_id_key on jcitems (id);
-