Thread: Executing SQL which is stored in database

/*
I have a customer requirement to provide for flexibility on how vacation
leave is calculated and am having trouble with executing the necessary
SQL which is stored in the database as a text column.

The employee table and a newly minted vacation leave formula table, shown
below, are related and identify what vacation leave calculation is used
for each employee (there are three different formulas for calculating
vacation leave).
*/


CREATE TABLE leave_computation_formula
(
  leave_computation_formula_pk serial NOT NULL,
  description varchar(64) NOT NULL,
  formula text,
  CONSTRAINT leave_computation_formula_pkey PRIMARY KEY
(leave_computation_formula_pk)
);

-- The three leave formulas:
-- The standard formula is (where hire_date is a column in employee)

INSERT INTO leave_computation_formula (description, formula) VALUES
(
'Standard',
'SELECT CASE
WHEN AGE(CURRENT_DATE, hire_date) < \'1 YEAR\' THEN 0
WHEN AGE(CURRENT_DATE, hire_date) < \'5 YEARS\' THEN  80
WHEN AGE(CURRENT_DATE, hire_date) < \'10 YEARS\' THEN 120
ELSE 160
END'
);

INSERT INTO leave_computation_formula (description, formula) VALUES
(
'Generous',
'SELECT 240'
);

INSERT INTO leave_computation_formula (description, formula) VALUES
(
'Negotiated accelerated vacation',
'SELECT CASE
WHEN AGE(CURRENT_DATE, hire_date) < \'1 YEARS\' THEN 0
WHEN AGE(CURRENT_DATE, hire_date) < \'10 YEARS\' THEN 120
ELSE 160
END'
);

ALTER TABLE employee ADD COLUMN leave_computation_formula_pk int4;

-- Assign everyone the standard formula

UPDATE employee SET leave_computation_formula_pk = 1;


-- Assign the one generous formula

UPDATE employee SET leave_computation_formula_pk = 2 WHERE (supplier_pk,
employee_pk)= (1, 55);


--Assign the negotiated formula

UPDATE employee SET leave_computation_formula_pk = 3 WHERE (supplier_pk,
employee_pk)= (1, 4);

/*
My problem is figuring out how, if at all possible, to execute the leave
calculation DML with the hire_date dependency. Obviously, given an
employee, specified by the compound key (supplier_pk, employee_pk), I can
read the correct formula from the  leave_computation_formula table:
*/

CREATE OR REPLACE FUNCTION computed_employee_leave(employee)
  RETURNS NUMERIC AS
'
DECLARE
  l_formula TEXT;
BEGIN
    SELECT INTO l_formula formula
        FROM leave_computation_formula
        JOIN employee USING (leave_computation_formula_pk)
        WHERE (supplier_pk, employee_pk) = ($1.supplier_pk, $1.employee_pk);

  -- Debugging...
  RAISE NOTICE \'%, %, %, %\', $1.supplier_pk, $1.employee_pk,
$1.hire_date, l_formula;

/*
   PROBLEM: HOW TO IMPLEMENT THE hire_date DEPENDENCY IN THE FORMULA
   The EXECUTE statement apparently cannot do variable substitution.
   Can you help?
*/
  EXECUTE l_formula;
END;
'
  LANGUAGE 'plpgsql' VOLATILE;



--Berend Tober