Executing SQL which is stored in database
От | |
---|---|
Тема | Executing SQL which is stored in database |
Дата | |
Msg-id | 64811.216.238.112.88.1082656102.squirrel@$HOSTNAME обсуждение исходный текст |
Список | pgsql-general |
/* 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
В списке pgsql-general по дате отправления: