1

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...

4
  • Select only needed columns in the subquery and apply row_to_json in outer query.
    – Akina
    Jul 6, 2022 at 11:18
  • Unrelated, but: row_to_json(OLD)::JSONB can be simplified to to_jsonb(old)
    – user1822
    Jul 6, 2022 at 11:25
  • @Akina, thanks, but I want to keep it generic - if someone would add columns to the table, I would want them to be included without changing the trigger.
    – TAZ
    Jul 6, 2022 at 12:31
  • Please supply a minimal reproducible example showing good and bad rows, along with your current trigger code Jul 6, 2022 at 14:32

1 Answer 1

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!

4
  • All this just to avoid listing the necessary columns explicitly?
    – mustaccio
    Jul 11, 2022 at 13:23
  • Indeed, very important for our application.
    – TAZ
    Jul 12, 2022 at 14:05
  • I sure hope performance is less important then.
    – mustaccio
    Jul 12, 2022 at 15:03
  • @mustaccio, the trigger is only on Update (and Delete, but we never delete from the table - just set an inactive boolean). Updates are also rarely done, but the trigger is quite crucial for when records are updated.
    – TAZ
    Jul 14, 2022 at 5:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.