Hello,

My principale database is Postgres. I use a other database (no postgres)
and i want "Synchronize" some tables. So i wrote a trigger in one table
to insert informatios about rows, table and id's of the table concerned
by the modifications . It work for one table but i want to use it for
more tables (a little more generic) I don't know how to change the name
of the primary key "NEW.ct_idm_" with the other primary key for the
other tables (not the same name)

could you help ? thank

the code is

-- Function: sync_update()
-- DROP FUNCTION sync_update();

CREATE OR REPLACE FUNCTION sync_update()
RETURNS trigger AS
$BODY$BEGIN
-- Ajoute une ligne dans sync_synchros pour refléter l'opération
réalisée


INSERT INTO sync_synchros (
sync_typeupdate,
sync_timestamp,
sync_tablename,
sync_numidm
)
VALUES (
TG_OP,
extract(epoch FROM now()),
TG_TABLE_NAME,
NEW.ct_idm_ <---- To change by the primary key concerned (don't know
how)
);
RETURN OLD;


RETURN NULL; -- le résultat est ignoré car il s'agit d'un trigger AFTER

END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE
COST 100;
ALTER FUNCTION sync_update() OWNER TO postgres;
GRANT EXECUTE ON FUNCTION sync_update() TO postgres;

JFT wrote:

I am not sure if I understood you right.

The above trigger function is working for you with a certain table.

Now you want to adapt it to another table.

Now what exactly is your problem? Is it difficult to replace "ct_idm_"
with a different primary key column name?

Maybe your problem is that you want to write a generic trigger that will
work for all tables without modifications. In that case the problem is not
so simple, because PL/pgSQL won't allow variables for column names.

If that is your problem, you should consider PL/Perl. It is more flexible
in that respect.

Yours,
Laurenz Albe

That is the problem
i prefer 1 trigger as 10

JFT wrote:
I see.

Then I'd use PL/Perl for the trigger.

Section 40.6 of the manual has a little example.

As you can see, $_TD->{new} is a hash in PL/Perl, and you can
use a string variable as a subscript.

To find out which column(s) make up the primary key, you can
use the system views information_schema.table_constraints and
information_schema.key_column_usage.

Yours,
Laurenz Albe