create or replace function stellenangebote_update_audit_handler ()
 returns trigger language plpgsql as $audit$
declare
 column_names text[] := array[
 'refnr',
 'beruf',
 'titel',
 'ort',
 'plz',
 'region',
 'land',
 'lat',
 'lon',
 'arbeitgeber',
 'veroeffentlichungsdatum',
 'modifikationstimestamp',
 'eintrittsdatum',
 'kundennummerhash',
 'externeurl'
 ];
 t text := null;
 is_equal boolean;
begin 
 foreach t in array column_names loop
 execute format('select (1ドル).%1$I is not distinct from (2ドル).%1$I', t)
 using old, new into is_equal;
 if not is_equal then 
 execute format('insert into stellenangebote_update_audit '
 '(job_refnr, column_name, old_value, new_value) values '
 '(1,ドル ''%1$s'', (2ドル).%1$I::text, (3ドル).%1$I::text)', t)
 using old.refnr, old, new;
 end if;
 end loop;
 return new;
end $audit$;

create table if not exists stellenangebote_update_audit (
 created timestamp primary key default current_timestamp,
 job_refnr text not null,
 column_name text not null,
 old_value text,
 new_value text
);
create table if not exists stellenangebote (
 refnr varchar(32) not null,
 beruf text null,
 titel text null,
 ort text null,
 plz ValidPLZ null,
 region text null, 
 land text null,
 lat numeric null,
 lon numeric null,
 arbeitgeber text null,
 veroeffentlichungsdatum date null,
 modifikationsTimestamp timestamp null,
 eintrittsdatum date null,
 kundennummerHash text null,
 externeUrl text null,
 created timestamp not null default current_timestamp,
 updated timestamp not null default current_timestamp,
 deleted timestamp null,
 archive_partition smallint not null default 0 check (archive_partition >= 0)
) partition by range(archive_partition);