summaryrefslogtreecommitdiff
path: root/sql/modules/Settings.sql
blob: 5b8d2b281c8685b48ceb9a934d022e98f15748f8 (plain)
  1. -- VERSION 1.3.0
  2. CREATE OR REPLACE FUNCTION setting_set (in_key varchar, in_value varchar)
  3. RETURNS VOID AS
  4. $$
  5. BEGIN
  6. UPDATE defaults SET value = in_value WHERE setting_key = in_key;
  7. RETURN;
  8. END;
  9. $$ language plpgsql;
  10. CREATE OR REPLACE FUNCTION setting_get (in_key varchar) RETURNS varchar AS
  11. $$
  12. DECLARE
  13. out_value varchar;
  14. BEGIN
  15. SELECT value INTO out_value FROM defaults WHERE setting_key = in_key;
  16. RETURN value;
  17. END;
  18. $$ LANGUAGE plpgsql;
  19. CREATE OR REPLACE FUNCTION setting_get_default_accounts ()
  20. RETURNS SETOF defaults AS
  21. $$
  22. DECLARE
  23. account defaults%ROWTYPE;
  24. BEGIN
  25. FOR account IN
  26. SELECT * FROM defaults
  27. WHERE setting_key like '%accno_id'
  28. LOOP
  29. RETURN NEXT account;
  30. END LOOP;
  31. END;
  32. $$ LANGUAGE plpgsql;
  33. CREATE OR REPLACE FUNCTION setting_increment (in_key varchar) returns varchar
  34. AS
  35. $$
  36. DECLARE
  37. base_value VARCHAR;
  38. raw_value VARCHAR;
  39. increment INTEGER;
  40. inc_length INTEGER;
  41. new_value VARCHAR;
  42. BEGIN
  43. SELECT value INTO raw_value FROM defaults
  44. WHERE setting_key = in_key
  45. FOR UPDATE;
  46. SELECT substring(raw_value from '(\\d*)(\\D*|<\\?lsmb [^<>] \\?>)*$')
  47. INTO base_value;
  48. IF base_value like '0%' THEN
  49. increment := base_value::integer + 1;
  50. SELECT char_length(increment::text) INTO inc_length;
  51. SELECT overlay(base_value placing increment::varchar
  52. from (select char_length(base_value)
  53. - inc_length + 1) for inc_length)
  54. INTO new_value;
  55. ELSE
  56. new_value := base_value::integer + 1;
  57. END IF;
  58. SELECT regexp_replace(raw_value, base_value, new_value) INTO new_value;
  59. UPDATE defaults SET value = new_value WHERE setting_key = in_key;
  60. return new_value;
  61. END;
  62. $$ LANGUAGE PLPGSQL;