summaryrefslogtreecommitdiff
path: root/utils/custom_fields
diff options
context:
space:
mode:
authoreinhverfr <einhverfr@4979c152-3d1c-0410-bac9-87ea11338e46>2006-09-21 03:49:28 +0000
committereinhverfr <einhverfr@4979c152-3d1c-0410-bac9-87ea11338e46>2006-09-21 03:49:28 +0000
commit0cc84fe90078cf649fd0f09bb53cf9fd276c1972 (patch)
treef14c261b7033fcfb1041045a8c8e8fec703258f9 /utils/custom_fields
parented264cee923434d54b35b8efd7924a6c937c682e (diff)
Custom fields database support is now an add-on.
git-svn-id: https://ledger-smb.svn.sourceforge.net/svnroot/ledger-smb/trunk@125 4979c152-3d1c-0410-bac9-87ea11338e46
Diffstat (limited to 'utils/custom_fields')
-rw-r--r--utils/custom_fields/custom_fields.sql47
1 files changed, 47 insertions, 0 deletions
diff --git a/utils/custom_fields/custom_fields.sql b/utils/custom_fields/custom_fields.sql
new file mode 100644
index 00000000..0ee4290b
--- /dev/null
+++ b/utils/custom_fields/custom_fields.sql
@@ -0,0 +1,47 @@
+
+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;
+
+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;