2

I am building an import/export interface on Postgres that requires semantic datamodel transformations. To facilitate the export, I created some views which provide me with a 1:1 match to my export model. In theory, I could use these views on imports as well by building a trigger structure behind it such as:

INSTEAD OF INSERT ON myview FOR EACH ROW EXECUTE FUNCTION myfunc

myfunc is an upsert function following the Schema

CREATE OR REPLACE FUNCTION myfunc()
RETURNS trigger AS
$BODY$
BEGIN
    UPDATE destination_table dt
    SET
    [semantic transformations]
    FROM (SELECT NEW.*) as vw_val
    LEFT JOIN value_list vl on vl.myval = vw_val.attr_val
    WHERE dt.obj_id=vw_val.obj_id;
    IF NOT FOUND THEN
    INSERT INTO destination_table 
    (
    [attributes]
    )
    (
    SELECT
    [semantic transformations]
    FROM (SELECT NEW.*) as vw_val
    LEFT JOIN value_list vl on vl.myval = vw_val.attr_val
    );
    END IF;
  RETURN NEW;   
END;
$BODY$
LANGUAGE plpgsql;

As the interface usually alters a set of records, it would be smart to use a statement level trigger. According to the postgres docs, there exists the possibility to create BEFORE and AFTER statement level triggers on views. As NEW and OLD are NULL on statement level, I would need a way to access all records that the interface tries to enter on my view. Is that possible?

3

0

Your Answer

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

Browse other questions tagged or ask your own question.