select get_pk_name(TG_TABLE_NAME) into pk_column_name;
if (TG_OP = 'INSERT' or TG_OP = 'UPDATE') then
execute 'select $1.' || quote_ident(pk_column_name) using new into pk_value;
else
execute 'select $1.' || quote_ident(pk_column_name) using old into pk_value;
end if;
mais ce qui me faut, c'est la valeur de la pk du record
n'était-ce ce
pk_value := old.pk_column_name; <------------------------------------------------------------------- Horreur !
tout irait bien
une petite idée ?
merci pour votre patience et votre dévouement
en fait, je viens de recevoir mes premiers bouquins sur pg seulement aujourd'hui
bonne soirée
]]>Pour un système d'audit, j'ai écrit
---------------------------------------------------------------------------------------------------------------------------------------------
-- create db objects
---------------------------------------------------------------------------------------------------------------------------------------------
drop table if exists record_tracking;
create table record_tracking (
id_record_tracking serial
,table_name varchar
,operation varchar
,pk_value varchar
,user_name varchar
,insertion_date timestamp default now()
);
drop sequence if exists record_tracking_seq;
create sequence record_tracking_seq;
drop table if exists app_table;
create table app_table (
pk integer primary key
,info varchar
);
---------------------------------------------------------------------------------------------------------------------------------------------
-- generic functions for triggers
---------------------------------------------------------------------------------------------------------------------------------------------
create or replace function get_pk_value(this_table_name name) returns varchar as $$
declare
pk_column_name varchar;
begin
select
c.column_name
into
pk_column_name
from
information_schema.table_constraints tc
join
information_schema.constraint_column_usage ccu
using
(constraint_schema, constraint_name)
join
information_schema.columns c
on
c.table_schema = tc.constraint_schema
and
tc.table_name = c.table_name
and
ccu.column_name = c.column_name
where
constraint_type = 'PRIMARY KEY'
and
tc.table_name = this_table_name;
return pk_column_name;
end;
$$ language 'plpgsql';
create or replace function record_tracking() returns trigger as $$
declare
pk_column_name varchar;
pk_value varchar;
begin
select get_pk_value(TG_TABLE_NAME) into pk_column_name;
pk_value := old.pk_column_name; <------------------------------------------------------------------- Horreur !
insert into record_tracking
(table_name, operation, pk_value_type, user_name)
values
(TG_TABLE_NAME, TG_OP, pk_value, current_user);
return new;
end;
$$ language 'plpgsql';
---------------------------------------------------------------------------------------------------------------------------------------------
-- create after insert trigger
---------------------------------------------------------------------------------------------------------------------------------------------
create trigger trg_app_table_record_tracking after insert or update or delete on app_table
for each row execute procedure record_tracking();
je ne vois pas comment, je pourrais résoudre ce problème.
Merci pour votre attention
]]>