#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2003
    Location
    Rostock - Germany
    Posts
    2
    Rep Power
    0

    Unhappy looking for Filter/ Replacing function/trigger ...


    Maybe someone knows a solution for the following problem or has written a function/ trigger allready..

    1. The Problem:
    I'm having some kind of a data security (privacy) problem. I have in my tables to many indicators doing the following by hand and so I tried to write a short trigger, function (okay I'm a newbie in database-administration). When I put in the output-database a value less than 4 into any field at any row, this value has to get 0 automaticly.

    2. My first idea was write a Trigger/Function - Construction for the database postgresql 7.3 database

    CREATE FUNCTION pg_clean()RETURNS TRIGGER AS'
    BEGIN
    if NEW=3 THEN set NEW=0;
    end if;
    if NEW=2 THEN set NEW=0;
    end if;
    if NEW=1 THEN set NEW=0;
    end if;
    END;'
    LANGUAGE 'plpgsql';

    CREATE TRIGGER clean AFTER INSERT OR UPDATE
    on test
    FOR EACH ROW
    EXECUTE PROCEDURE pg_clean();


    3. error: NEW used in none query rule
    (looks like a deadlook as far as I read the manuals)

    4. Has somebody a solution?

    best regards and thx
    marcuse
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2003
    Posts
    373
    Rep Power
    11
    when you do NEW you have to specify the attribute name of the triggered record that you're referring to. For instance, if the table has two columns, pk_val and data_val you would have to do

    NEW.pk_val = 3 THEN NEW.pk_val = 0

    I don't think you need to say RETURN NEW because you did return type as TRIGGER, but you may want to do RETURNS OPAQUE AS ''; in stead, that's just a matter of preference though to me it's easier to follow that way. I think both are supported after 7.0.x
  4. #3
  5. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2003
    Location
    Rostock - Germany
    Posts
    2
    Rep Power
    0

    I'm looking more for a dynamic solution


    They way you suggest is of cause right, but I still have the problem that actually in the database are 24 tables with maybe more than 600 columns, doing all this by hand is really hard work. Is their no other way than doing this routine like:

    IF NEW.social_aid=3 THEN social_aid=0;

    I still believe there is a more sexy functionů

    marcuse

IMN logo majestic logo threadwatch logo seochat tools logo