DROP TABLE Person;
CREATE TABLE Person
(
per_ID INTEGER,
per_FName VARCHAR(35) NOT NULL,
per_LName VARCHAR(40) NOT NULL,
per_Age INTEGER NOT NULL,
per_Salary INTEGER NOT NULL,
per_Dependency VARCHAR(40) NOT NULL,
per_Charge VARCHAR(30) NOT NULL,
per_CityofBirth VARCHAR(50) NOT NULL,
per_FounderOf VARCHAR(30) NOT NULL,
PRIMARY KEY(per_ID));
CREATE OR REPLACE FUNCTION GetPerson
(
Person.per_id%TYPE
)
RETURNS SETOF Person AS
$$
BEGIN
RETURN QUERY
SELECT ALL *
FROM Person
WHERE (per_Id=$1);
END;
$$LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION GetFirstPerson
(
)
RETURNS SETOF Person AS
$$
BEGIN
RETURN QUERY
SELECT ALL *
FROM Person
ORDER BY per_Id ASC
LIMIT 1;
END;
$$LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION GetLastPerson
(
)
RETURNS SETOF Person AS
$$
BEGIN
RETURN QUERY
SELECT ALL *
FROM Person
ORDER BY per_Id DESC
LIMIT 1;
END;
$$LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION GetPreviousPerson
(
Person.per_ID%TYPE
)
RETURNS SETOF Person AS
$$
BEGIN
RETURN QUERY
SELECT ALL *
FROM Person
WHERE (per_ID<$1)
ORDER BY per_ID DESC
LIMIT 1;
END;
$$LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION GetNextPerson
(
Person.per_ID%TYPE
)
RETURNS SETOF Person AS
$$
BEGIN
RETURN QUERY
SELECT ALL *
FROM Person
WHERE (per_ID>$1)
ORDER BY per_ID ASC
LIMIT 1;
END;
$$LANGUAGE plpgsql;
CREATE OR REPLACE function addPerson
(
Person.per_id%TYPE,
Person.per_fname%TYPE,
Person.per_lname%TYPE,
Person.per_age%TYPE,
Person.per_salary%TYPE,
Person.per_dependency%TYPE,
Person.per_charge%TYPE,
Person.per_cityofbirth%TYPE,
Person.per_founderof%TYPE
)
RETURNS VOID AS
$$
BEGIN
INSERT INTO Person VALUES($1,$2,$3,$4,$5,$6,$7,$8,$9);
END;
$$LANGUAGE plpgsql;
CREATE OR REPLACE function UpdatePerson
(
Person.per_id%TYPE,
Person.per_fname%TYPE,
Person.per_lname%TYPE,
Person.per_age%TYPE,
Person.per_salary%TYPE,
Person.per_dependency%TYPE,
Person.per_charge%TYPE,
Person.per_cityofbirth%TYPE,
Person.per_founderof%TYPE
)
RETURNS VOID AS
$$
BEGIN
UPDATE Person set per_fname=$2,per_lname=$3,per_age=$4,per_salary=$5,per_depen dency=$6,per_charge=$7,per_cityofbirth=$8,per_founderof=$9
WHERE per_id=$1;
END;
$$LANGUAGE plpgsql;
CREATE OR REPLACE function DeletePerson
(
Person.per_id%TYPE
)
RETURNS VOID AS
$$
BEGIN
DELETE FROM Person
WHERE (per_id=$1);
END;
$$LANGUAGE plpgsql;

INSERT INTO Person VALUES(1,'Stephen','Wozniac',40,400000,'Generacion','Tecnico ','San Jose','Apple Inc');
INSERT INTO Person VALUES(5978744,'Efren','Suarez Peralta',37,1200000,'Operacion','Auxiliar Tecnico','Riosucio','Chec S.A');
INSERT INTO Person VALUES(33991804,'Luz Omaira','Izquierdo Ossa',37,950000,'Gerencia','Secretaria','Manizales','Comcel' );