how to populate data using PostgreSQL trigger function

Question

i have a code trigger function in postgres

DECLARE
  v_log_header_id int := 0;
BEGIN
  SELECT COUNT(well_id) + 1 INTO v_log_header_id FROM log_header 
  WHERE well_id= NEW.well_id AND log_type = NEW.log_type;
  IF v_log_header_id is null THEN
    NEW.log_header_id := NEW.well_id || '_' ||(SELECT value FROM lookup_ref WHERE value_description = NEW.log_type AND table_name = 'log_header' and column_name ='log_type')  || '<' || to_char(1, 'fm00')  || '>';
  ELSE
    NEW.log_header_id := NEW.well_id || '_' ||(SELECT value FROM lookup_ref WHERE value_description = NEW.log_type AND table_name = 'log_header' and column_name ='log_type') || '<' || to_char(v_log_header_id, 'fm00') || '>';
  END IF;
RETURN NEW;
END;

and a code trigger function is created to autofill the log_header_id my table

this is my query table

-- Table: public.log_header

-- DROP TABLE IF EXISTS public.log_header;

CREATE TABLE IF NOT EXISTS public.log_header
(
    log_header_id character varying(64) COLLATE pg_catalog."default" NOT NULL,
    well_id character varying(64) COLLATE pg_catalog."default" NOT NULL,
    log_type character varying(64) COLLATE pg_catalog."default" NOT NULL,
    run_no smallint NOT NULL,
    log_direction character varying(64) COLLATE pg_catalog."default",
    well_state character varying(64) COLLATE pg_catalog."default" NOT NULL,
    wh_press numeric(30,1),
    mud character varying(64) COLLATE pg_catalog."default" NOT NULL,
    log_datum character varying(64) COLLATE pg_catalog."default" NOT NULL,
    date_logging date NOT NULL,
    logging_company character varying(64) COLLATE pg_catalog."default" NOT NULL,
    remarks text COLLATE pg_catalog."default",
    row_changed_by character varying(64) COLLATE pg_catalog."default" NOT NULL DEFAULT CURRENT_USER,
    updated_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    row_created_by character varying(64) COLLATE pg_catalog."default" NOT NULL DEFAULT CURRENT_USER,
    created_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT log_header_pkey PRIMARY KEY (log_header_id),
    CONSTRAINT log_header_well_id_fkey FOREIGN KEY (well_id)
        REFERENCES public.well_header (well_id) MATCH FULL
        ON UPDATE CASCADE
        ON DELETE RESTRICT
)

TABLESPACE pg_default;

ALTER TABLE IF EXISTS public.log_header
    OWNER to postgres;

-- Trigger: trigger_log_header

-- DROP TRIGGER IF EXISTS trigger_log_header ON public.log_header;

CREATE TRIGGER trigger_log_header
    BEFORE INSERT
    ON public.log_header
    FOR EACH ROW
    EXECUTE FUNCTION public.func_log_header_id();

My question is, how do I autofill the data in the run_no column to be auto like log_header_id with the record run_no following the number from within LGK-01_CAL<...>

I've tried using sequences, but it doesn't satisfy me. because, if i use run_no sequence is not equal to number in log_header_id LGK_01_CAL<...>

Answer 1

DECLARE
  v_log_header_id int := 0;
  v_run_no int := 0;
BEGIN
  SELECT COUNT(well_id) + 1, COUNT(run_no) + 1 INTO v_log_header_id, v_run_no FROM log_header 
  WHERE well_id= NEW.well_id AND log_type = NEW.log_type;
  IF v_log_header_id is null and v_run_no is null THEN
    NEW.run_no := to_char(1, 'fm00');
    NEW.log_header_id := NEW.well_id || '_' ||(SELECT value FROM lookup_ref WHERE value_description = NEW.log_type AND table_name = 'log_header' and column_name ='log_type')  || '<' || to_char(1, 'fm00')  || '>';
  ELSE
    NEW.run_no := to_char(v_run_no, 'fm00');
    NEW.log_header_id := NEW.well_id || '_' ||(SELECT value FROM lookup_ref WHERE value_description = NEW.log_type AND table_name = 'log_header' and column_name ='log_type') || '<' || to_char(v_log_header_id, 'fm00') || '>';
  END IF;
RETURN NEW;
END;