admin | 
09 29th, 2008| 
Instrucţiunile SELECT şi funcţiile PL/pgSQL
Imaginaţi-vă un tabel ce conţine numele unui angajat, numărul camerei în care lucrează angajatul şi salariul acestuia:
CREATE TABLE employees (id serial, name varchar(50), room int4, salary int4);
INSERT INTO employees (name, room, salary) VALUES (’Paul’, 1, 3000);
INSERT INTO employees (name, room, salary) VALUES (’Ion’, 1, 3000);
INSERT INTO employees (name, room, salary) VALUES (’Linda’, 1, 3000);
INSERT INTO employees (name, room, salary) VALUES (’Carla’, 1, 3000);
INSERT INTO employees (name, room, salary) VALUES (’Maria’, 1, 3000);
INSERT INTO employees (name, room, salary) VALUES (’Alice’, 1, 3000);
INSERT INTO employees (name, room, salary) VALUES (’Victor’, 1, 3000);
Scopul nostru este să scriem o funcţie care să verifice dacă un angajat se află în tabel şi se execută o operaţie INSERT sau o operaţie UPDATE:
Tags: ALIAS, BEGIN, CREATE, ELSE, EXCEPTION, PostgreSQL, triggerCREATE FUNCTION insertupdate(text, int4) RETURNS bool AS ’
DECLARE
intext ALIAS FOR $1;
newsal ALIAS FOR $2;
checkit record;
BEGIN
SELECT INTO checkit * from employees
WHERE name = intext;
IF NOT FOUND THEN
INSERT INTO employees (name, room, salary)
VALUE(intext, ’’1’’, newsal);
RETURN ’’t’’;
ELSE
UPDATE employees SET salary=newsal, room=checkit.room
WHERE name=intext;
RETURN ’’f’’;
ENDIF;
END;
’ LANGUAGE ’plpgsql’;