database.txt

-- this table holds the list of to-be-sent documents

CREATE TABLE docstosend
(
  id serial NOT NULL,
  accession character varying(50) NOT NULL,
  doc_name character varying(50),
  doc_header bytea,
  doc_body bytea,
  doc_footer bytea,
  doc_type character varying(10),
  created timestamp without time zone DEFAULT now(),
  sent boolean DEFAULT false,
  sent_date timestamp without time zone,
  CONSTRAINT docstosend_pkey PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE docstosend
  OWNER TO griensu;

-- Crear el siguiente trigger en la tabla turno:

CREATE OR REPLACE FUNCTION addtodocstosend()
  RETURNS trigger AS
$BODY$
declare
  docs RECORD;
begin
  -- get documents
  for docs in 
    select 
      d.nombredocumento,
      d.cabeceradocumento,
      d.cuerpodocumento,
      d.piedocumento
    from turnodocumento d
    where d.idturno = NEW.idturno and NEW.estadoinfres='D'
  LOOP
    insert into docstosend(accession, doc_name, doc_header, doc_body, doc_footer, doc_type)
      values(
        NEW.turnosistemaexterno, 
        docs.nombredocumento, 
        docs.cabeceradocumento,
        docs.cuerpodocumento,
        docs.piedocumento,
        'rtf');
  END LOOP;
  return null;
end;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION addtodocstosend()
  OWNER TO postgresql;

create trigger tr_addtodocstosend
  after update on turno
  for each row
  execute procedure addtodocstosend();