Executing SQL which is stored in database

[btober]

/*
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

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org