diff options
Diffstat (limited to 'sql/Pg-functions.sql')
-rwxr-xr-x | sql/Pg-functions.sql | 41 |
1 files changed, 41 insertions, 0 deletions
diff --git a/sql/Pg-functions.sql b/sql/Pg-functions.sql index 4eea5011..e3689fd6 100755 --- a/sql/Pg-functions.sql +++ b/sql/Pg-functions.sql @@ -268,3 +268,44 @@ END; CREATE TRIGGER parts_short AFTER UPDATE ON parts FOR EACH ROW EXECUTE PROCEDURE trigger_parts_short(); + +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 + INSERT INTO custom_table_catalog (extends) VALUES (table_name); + EXECUTE ''CREATE TABLE custom_''||table_name || '' ()''; + 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 '' || field_name; + RETURN TRUE; +END; +' LANGUAGE PLPGSQL; |