Triggers: List of ALL the OLD record's column names

Question

I have a trigger that creates a jsonb field of all the OLD columns of the relevant table in an audit table. However, I need to exclude any geometry columns, in my case, the column name being the_geom. Currently this is how we do it: '''row_to_json(OLD)::JSONB - 'the_geom''''. However, some records have rubbish geometry which causes this statement to fall over (seeing it tries to convert/translate the geometry into jsonb, before it excludes it). So I want to get all the OLD columns (both names and values) so I can dynamically create the jsonb column. Is this possible in Posgres11?

The road table:

CREATE EXTENSION if not exists postgis;
drop TABLE if exists public.the_roads;
CREATE TABLE public.the_roads (
    id integer NOT NULL,
    begin_km double precision,
    end_km double precision,
    the_geom geometry NULL,
    CONSTRAINT pk_the_roads PRIMARY KEY (id));

SP called by the trigger:

CREATE OR REPLACE FUNCTION public.trg_sp_public_the_roads()
     RETURNS trigger
     LANGUAGE plpgsql
     SECURITY DEFINER
    AS $function$
    BEGIN
        INSERT INTO public.hist_the_roads (jb_old_row, change_date, change_type)
        VALUES
          (to_jsonb(OLD) - 'the_geom',  -- T H I S   L I N E   I S   W H E R E   E R R O R   O C C U R S
          now(),
          'UPDATE');
        RETURN NEW;
    END
    $function$;

Trigger:

drop trigger if exists trg_audit_public_the_roads on the_roads;
create trigger trg_audit_public_the_roads before
update
    on
    public.the_roads for each row execute function 
        public.trg_sp_public_the_roads();
        --public.trg_sp_public_the_roads__new();

Audit Table (populated by trigger)

DROP TABLE IF EXISTS public.hist_the_roads;
CREATE TABLE public.hist_the_roads (
    id serial4 NOT NULL,
    jb_old_row jsonb NULL,
    change_type text NULL,
    change_date timestamptz NULL,
    CONSTRAINT pk_hist_the_roads PRIMARY KEY (id)
);

Insert and update queries (the last one causes the error)

insert into the_roads (id, begin_km, end_km, the_geom) values (1, 10.5, 11.0, 'SRID=4326;LINESTRING (19.445210869123635 -30.96236575555145, 19.444319674004173 -30.96235579806408)');
-- update the_geom with invalid geometry type for jsonb. NB: This does not cause failure, seeing the trigger store s the OLD geometry in the jsonb field
update the_roads 
set the_geom = st_forcecurve('SRID=4326;POLYGON ((20.51483350500007 -29.712817192999978, 20.531559995000066 -29.705819993999967, 20.51147001100003 -29.70864999799994, 20.51483350500007 -29.712817192999978))')
where id = 1;    
-- update the_geom again. NB: Seeing the trigger writes the OLD geometry into jsonb, this is the instruction that fails with this error:
--
--                          SQL Error [XX000]: ERROR: lwgeom_to_geojson: 'CurvePolygon' geometry type not supported                 --
--
update the_roads 
set the_geom = 'SRID=4326;LINESTRING (19.445210869123635 -30.96236575555145, 19.444319674004173 -30.96235579806408)'
where id = 1;

Attempt at new SP to build jsonb 'manually' (vs using to_jsonb())

CREATE OR REPLACE FUNCTION public.trg_sp_public_the_roads__new()
 RETURNS trigger
 LANGUAGE plpgsql
 SECURITY DEFINER
AS $function$
DECLARE
    myrow record;
    jb jsonb;
    i int = 0;
    atext text; 
BEGIN
    for myrow in
        select cols.column_name, *
        from information_schema.columns cols 
        WHERE cols.table_schema = 'public' 
        and cols.table_name='the_roads'
        and cols.udt_name <> 'geometry'
    loop 
        i = i + 1;
--      execute format('select r.%s::varchar from rrs_road r where r.id = 1',myrow.column_name) into atext; 
    
        -- the following statement I'm trying to get the actual values of OLD.id, OLD.begin_km etc, but of course, it doesn't work
        -- execute format('OLD.%s',myrow.column_name) into atext;   
    
        -- if one could do a select on OLD, this WOULD work...
        execute format('select OLD.%s from OLD',myrow.column_name) into atext;  -- so this also falls over because one cannot select from "OLD". This is where I would appreciate help...
    
        if i = 1 then 
            jb = jsonb_build_object(myrow.column_name, atext);
        else 
            jb = jb||jsonb_build_object(myrow.column_name, atext); 
        end if; 
    end loop;

    INSERT INTO public.hist_the_roads (jb_old_row, change_date, change_type)
    values (
      jb,--(to_jsonb(OLD) - 'the_geom',
      now(),
      'UPDATE');

    RETURN NEW;

END
$function$;

Edited to include code sample...

Answer 1

I finally solved it by letting the trigger create a temporary table inserting the OLD.* values.

create temp table tmproad as select old.*;

Then I dropped the geometry column:

alter table tmproad drop column if exists the_geom;

I built a jsonb column from the rest of the columns:

select to_jsonb(t) from tmproad t

Thanks to those who attempted to help. Thanks for the code simplification!