Database Reference
In-Depth Information
AND upper(emp_name) !=
upper(CURRENT_USER); - don't log select of own
salary
-- return the requested salary
SELECT salary FROM salaries WHERE
upper(emp_name) = upper($1);
$$ LANGUAGE SQL SECURITY DEFINER;
Notice that we implemented a "soft security" approach, where you can look up for
other people's salaries, but you have to do it responsibly, that is, only when you need
to as your manager will know that you have checked.
The
set_salary()
function abstracts away the need to check if the user exists; if
the user does not, it is created. Setting someone's salary to
0
will remove him from
the salary table. Thus, the interface is much simplified and the client application of
these functions needs to know and do less:
CREATE OR REPLACE FUNCTION
set_salary(i_emp_name text, i_salary int)
RETURNS TEXT AS $$
DECLARE
old_salary integer;
BEGIN
SELECT salary INTO old_salary
FROM salaries
WHERE upper(emp_name) = upper(i_emp_name);
IF NOT FOUND THEN
INSERT INTO salaries VALUES(i_emp_name,
i_salary);
INSERT INTO
salary_change_log(salary_op,emp_name,new_salary)
VALUES ('INSERT',i_emp_name,i_salary);
RETURN 'INSERTED USER ' || i_emp_name;
ELSIF i_salary > 0 THEN
UPDATE salaries
SET salary = i_salary
WHERE upper(emp_name) = upper(i_emp_name);
