From d1eef00925161c3fc0a148a44bd2d88c864e81ef Mon Sep 17 00:00:00 2001
From: einhverfr <einhverfr@4979c152-3d1c-0410-bac9-87ea11338e46>
Date: Wed, 1 Nov 2006 05:24:34 +0000
Subject: 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
---
 sql/Pg-functions.sql | 320 ---------------------------------------------------
 1 file changed, 320 deletions(-)
 delete mode 100755 sql/Pg-functions.sql

(limited to 'sql/Pg-functions.sql')

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
-- 
cgit v1.2.3