Best way to get Static data inside before trigger

Question

I defined the below plpgsql function (Before trigger) to update the data if one of the column provided , I will get the other one from static data. But I want to know what is the best way to get the static data for given "columna", I need to get the "columnb" from static_tbl (more like dictionary / map datastrcuture). At the moment I am querying inside the Trigger function on the static table .

I know that i can use the case statement as well instead of querying . Please let me know what will be the best fit here. Adding the test data Say ColumnA contains 'a' it will be propagated to ColumnB as 'Alphabet A' Otherwise ColumnB contains 'Alphabet A' it will be propagated to ColumnA as 'a' . What is the best way to store the static data used for conversion columnA value to columnB value?

CREATE FUNCTION update_tblname_column_b () RETURNS TRIGGER AS $$
        BEGIN                                                                        
        IF NEW IS NULL THEN                                                               
            RAISE EXCEPTION 'this function cannot be installed with a DELETE trigger';
    END IF;
    IF NEW.column_b IS NOT NULL AND NEW.column_a IS NULL THEN
        NEW.column_a = (select column_a from static_tbl where column_b = NEW.column_b);
    ELSIF NEW.column_a IS NOT NULL AND NEW.column_b IS NULL THEN
        NEW.column_b = (select column_b from static_tbl where column_b = NEW.column_a);
    END IF;
    RETURN NEW;
    END;

$$ LANGUAGE PLPGSQL;

CREATE TRIGGER populate_column
                            BEFORE INSERT OR UPDATE ON creatives FOR each ROW
                            EXECUTE PROCEDURE update_tblname_column_b();

Answer 1

If column_a is functionally dependent on column_b (or vice versa) in a stable manner the best way is not to store the functionally dependent value at all. Look it up on the fly. Maybe create a view (or materialized view) for convenience. Possibly ensure referential integrity with a FOREIGN KEY constraint. Incorporate the look-up in your input logic. Then you don't need a trigger.

If you actually need both columns and a trigger, fix a sneaky bug: You declare variables column_a and column_b, those are visible in the function body everywhere. When you use the identical column names column_a and column_b without table qualification you produce a naming conflict. Related:

• Naming conflict between function parameter and result of JOIN with USING clause

The solution in your case is simple: don't declare the variables to begin with, you have no use for them. The general solution is to always table-qualify column names if conflicts are possible. I implemented both:

CREATE OR REPLACE FUNCTION update_tblname_column_b()
  RETURNS TRIGGER AS $$
BEGIN                                                                        
   IF TG_OP = 'DELETE' THEN
      RAISE EXCEPTION 'This function cannot be used for  DELETE trigger.';
   END IF;

IF NEW.column_b IS NOT NULL AND NEW.column_a IS NULL THEN
   SELECT INTO NEW.column_a  s.column_a
   FROM   static_tbl s
   WHERE  s.column_b = NEW.column_b;

ELSIF NEW.column_a IS NOT NULL AND NEW.column_b IS NULL THEN
   SELECT INTO NEW.column_b  s.column_b
   FROM   static_tbl s
   WHERE  s.column_a = NEW.column_a;
END IF;

RETURN NEW;

END
$$ LANGUAGE plpgsql;

Also, don't check for NULL to identify the trigger type, this can be corner-case incorrect. Check the special variable TG_OP instead:

• Can a function detect the trigger event type?

---

An alternative way to store lookup values would be the enum data type. But I wouldn't use enum for more than a couple of options and only if they hardly ever change. Actually, I prefer lookup tables with FK constraints.

Answer 2

I think your method of creating a dictionary using a table structure like that would work, though the else clause doesn't quite make sense to me. Did you mean "WHERE column_a = NEW.column_a"? Anyway, this might be slightly simpler:

CREATE FUNCTION update_tblname_column_b () RETURNS TRIGGER AS $$
    DECLARE 
        column_a tblname.column_a%TYPE;
        column_b tblname.column_b%TYPE;
    BEGIN                                                                        
        IF NEW IS NULL THEN                                                               
            RAISE EXCEPTION 'this function cannot be installed with a DELETE trigger';
    END IF;

    NEW.column_a = COALESCE(NEW.column_a, (SELECT column_a FROM static_tbl WHERE column_b = NEW.column_b));
    NEW.column_b = COALESCE(NEW.column_b, (SELECT column_b FROM static_tbl WHERE column_a = NEW.column_a));

    RETURN NEW;
END;

$$ LANGUAGE PLPGSQL;

CREATE TRIGGER populate_column
    BEFORE INSERT OR UPDATE ON creatives FOR each ROW
        EXECUTE PROCEDURE update_tblname_column_b();

The COALESCE function returns the first non-null argument in its list. If all arguments are NULL, then it returns NULL.

Depending on how you intend to use this, you might be better off using some of the new JSON capabilities in postgres 9.3 and later. That would give you another way of creating key/value pairs.