drop function insert_job (int4,text,text);
CREATE FUNCTION insert_job (int4,text,text) RETURNS int2 AS '
DECLARE
    c_id_ins int4; j_name_ins text;  l_ins text; 
    job_id1 int4; oid1 int4; test_id int4 := 0; j_no_ins int4 := 0;
    record1 RECORD; record2 RECORD; record3 RECORD; record4 RECORD;
BEGIN
   j_name_ins := $2; l_ins  := $3; c_id_ins := $1;

     -- Ejecutamos algunos procedimientos Perl ahora. Estos son ejemplos 
     -- de procedimientos Perl.
     -- Limpia el nombre de job.
   SELECT INTO record4 clean_text(j_name_ins) as text1;
   j_name_ins = record4.text1;
     -- Limpia la localización de job.
   SELECT INTO record4 clean_text(l_ins) as text1;
   l_ins = record4.text1;
     -- Verifica que los valores que introducimos son correctos.
   SELECT INTO record4 job_values_verify (c_id_ins, j_name_ins, l_ins) as no;
   IF record4.no < 0 THEN return (record3.no); END IF;

     -- Mira si tenemos nombres únicos, de otro manera devuelve 0.
   FOR record1 IN SELECT job_id FROM jobs  
      where contact_id = c_id_ins and job_name = j_name_ins
      LOOP
      test_id := record1.job_id;
   END LOOP;
     -- Si job_id es nulo, genial, de otro modo suspende y devuelve -1;
   IF test_id > 0 THEN return (-1); END IF;

   FOR record3 IN SELECT max(job_no) from jobs_backup where contact_id = c_id_ins
      LOOP
      IF record3.max IS NULL THEN j_no_ins := 0; END IF;
      IF record3.max > -1 THEN j_no_ins = record3.max + 1; END IF;
   END LOOP;

     -- Se insertan los valores. Deja a la secuencia determinar job_id.
   insert into jobs (contact_id, job_no, job_name, job_location)
        values (c_id_ins, j_no_ins, j_name_ins, l_ins);
     -- Obtener el oid único de la fila que se acaba de insertar.
   GET DIAGNOSTICS oid1 = RESULT_OID;
     -- Obtener el id de job. No usar SELECT INTO, ya que record2 necesita ser asignado.
   FOR record2 IN SELECT job_id FROM jobs where oid = oid1
      LOOP
      job_id1 := record2.job_id;
   END LOOP;
   
     -- Si job_id1 es NULO, la inserción falló o algo va mal.
   IF job_id1 is NULL THEN return (-2); END IF;
     -- Debería también ser mayor que 0, de otro modo algo va mal.
   IF job_id1 < 1 THEN return (-3); END IF;

     -- Todo ha terminado, devuelve job_id1 como job_id.
   insert into jobs_backup (contact_id, job_no, job_name, job_location, action, error_code)
        values (c_id_ins, j_no_ins, j_name_ins, l_ins, ''insert'', job_id1);
   return (job_id1);
END;
' LANGUAGE 'plpgsql';
select insert_job (1,'Job Title 1','Boston, MA');
select insert_job (1,'Job Title 2','San Jose, CA');
select insert_job (2,'Job Title 1','Columbus, Ohio');
select insert_job (2,'Job Title 2','Houston, TX');
select insert_job (3,'Job Title 1','Denver, CO');
select insert_job (3,'Job Title 2','New York, NT');
select * from jobs;