summaryrefslogtreecommitdiff
path: root/sql/Pg-functions.sql
diff options
context:
space:
mode:
Diffstat (limited to 'sql/Pg-functions.sql')
-rwxr-xr-xsql/Pg-functions.sql320
1 files changed, 0 insertions, 320 deletions
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
generated by cgit v1.2.3 (git 2.39.1) at 2024-07-01 11:48:00 +0000