Триггер plpgsql

-- Function: ins_n()
 
-- DROP FUNCTION ins_n();
 
CREATE OR REPLACE FUNCTION ins_n()
  RETURNS TRIGGER AS
$BODY$
BEGIN
 
--insert into out (
--  id_doc,с_pt,doc_creation_time,id,
--  name,number,
--  e,
--  start,end
--)
INSERT INTO OUT (
  id_doc,c_pt,doc_creation_time,id,
  e,
  START,END
)
[COLOR="Blue"]SELECT 'P'||SUBSTRING(e.code FROM 2) AS id_doc[/COLOR],'1234' AS c_pt,
       now() AS doc_creation_time,i.id AS id,
f.e AS e,
(f.dt_range).start AS START,(f.dt_range).final AS final
FROM fr a
LEFT OUTER JOIN m b ON a.m_id=b.id
LEFT OUTER JOIN ta_an c ON b.t_id=c.t_id
LEFT OUTER JOIN ta_pa e ON b.t_id=e.id
LEFT OUTER JOIN res f ON b.t_id=f.t_id
INNER JOIN s_con h ON e.num=h.p_num[1]
INNER JOIN rks.p_ut i ON i.id_in=h.plan_in_num AND i.id_doc IS NOT NULL
WHERE a.id=NEW.id;
 
INSERT INTO out_p (
  id_doc,
  fr,ba,bad,
  den,DEC,ng,
  mg,pl,
  s,s_r,
  m_t,e_c,
  dn,m_d,
  s_t
)
[COLOR="blue"]SELECT 'P'||SUBSTRING(e.code FROM 2) AS id_doc[/COLOR],
(fr).fr AS fr,(fr).ba AS ba,(fr).bad AS bad,
a.den AS den, a.dec AS DEC, a.ng AS ng,
a.mg AS mg, a.pl AS pl,
a.s AS s, a.s_r AS s_r,
a.m_t AS m_t, a.e_c AS e_c,
a.dn AS dn, a.m_d AS m_d,
a.s_t AS s_t
FROM fr a
LEFT OUTER JOIN m b ON a.m_id=b.id
LEFT OUTER JOIN ta_an c ON b.t_id=c.t_id
LEFT OUTER JOIN ta_pa e ON b.t_id=e.id
LEFT OUTER JOIN res f ON b.t_id=f.t_id
INNER JOIN s_con h ON e.num=h.p_num[1]
INNER JOIN p_ut i ON i.id_in=h.p_num AND i.id_doc IS NOT NULL
WHERE a.id=NEW.id;
 
RETURN NEW;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION ins_n()
  OWNER TO adm;
COMMENT ON FUNCTION ins_n() IS 'Тест';

-- структура тестовой таблицы
CREATE SEQUENCE sh_test.tbl_docs_id_seq;
ALTER SEQUENCE sh_test.tbl_docs_id_seq
 INCREMENT BY 1
 MINVALUE 1
 MAXVALUE 9223372036854775807
 START WITH 1
 NO CYCLE;
ALTER SEQUENCE sh_test.tbl_docs_id_seq OWNER TO postgres;
CREATE TABLE sh_test.tbl_docs (
    id BIGINT NOT NULL,
    name_doc text NOT NULL,
    body_doc text,
    num_doc text,
    date_doc TIMESTAMP
);
COMMENT ON TABLE sh_test.tbl_docs IS 'Документы и номера.';
ALTER TABLE sh_test.tbl_docs ALTER id 
SET DEFAULT NEXTVAL('sh_test.tbl_docs_id_seq'::regclass);
ALTER TABLE sh_test.tbl_docs ALTER name_doc SET DEFAULT ''::text;
ALTER TABLE sh_test.tbl_docs ALTER date_doc SET DEFAULT now();
ALTER TABLE sh_test.tbl_docs ADD 
CONSTRAINT tbl_docs_pkey PRIMARY KEY (id);
CREATE INDEX tbl_docs_date_doc_idx ON sh_test.tbl_docs USING btree (date_doc);
ALTER TABLE sh_test.tbl_docs OWNER TO postgres;

CREATE OR REPLACE FUNCTION sh_test.fn_test_set_num(in_id BIGINT)
 RETURNS BIGINT
 LANGUAGE plpgsql
 SECURITY DEFINER
AS $function$
DECLARE
    v_rec RECORD; 
    v_date_doc TIMESTAMP;
    v_num_doc TEXT;
    v_num_row INTEGER;
BEGIN
    SELECT date_doc INTO v_date_doc
    FROM sh_test.tbl_docs
    WHERE id = in_id;
    FOR v_rec IN
        SELECT
            id,
            date_doc,
            num_doc
        FROM sh_test.tbl_docs
        WHERE date_doc::DATE = v_date_doc::DATE 
        ORDER BY date_doc ASC 
        FOR UPDATE
    LOOP
        v_num_row = COALESCE(v_num_row,0)+1;
        IF v_rec.id = in_id THEN -- тут получаем тот номер
            v_num_doc = v_date_doc::DATE::TEXT||' '||v_num_row::TEXT;
        END IF;
    END LOOP;
    UPDATE sh_test.tbl_docs SET num_doc=v_num_doc WHERE id = in_id;
    RETURN in_id;
END;
$function$;
ALTER FUNCTION sh_test.fn_test_set_num(BIGINT) OWNER TO postgres;
GRANT EXECUTE ON FUNCTION sh_test.fn_test_set_num(BIGINT) TO PUBLIC;
GRANT EXECUTE ON FUNCTION sh_test.fn_test_set_num(BIGINT) TO postgres WITH GRANT OPTION;

SELECT                                    
id,
date_doc,
num_doc
FROM sh_test.tbl_docs
ORDER BY date_doc ASC;