Pull-forward¶

A technique for populating recursive hierarchies, where any attributes that are specified on the parent entity but not on the child entity are pulled forward into the child entity by a trigger. When querying the database, this avoids the need to join to the parent entity to get all the attributes, because the attributes have been made directly available in the child entity.

This creates the effect of the CSS inherit keyword, which causes a style to be inherited from the parent element. It is also similar to prototype inheritance in JavaScript, but copies the inherited attributes to the subclass object.

alternatives¶

If you are able to refactor the table in question, it is often easier to instead factor out the common attributes into a side table (using the flyweight pattern). This avoids the need to maintain trigger code for every shared attribute. (You would, however, need to include a pull-forward for the pointer to the side table.)

in VegBIEN¶

This technique should be used to populate the VegCore/VegBIEN sampling event hierarchy, where a nested event needs to copy missing event attributes from the parent event. (The VegBIEN sampling event hierarchy is currently missing this, which significantly complicates the denormalization code upon left-join, such as in analytical_stem_view.)

implementation¶

placeholders: table, parent_pointer, pulled_field

CREATE OR REPLACE FUNCTION table_pull_forward_from_parent()
  RETURNS trigger AS
$BODY$
DECLARE
    parent table;
BEGIN
    SELECT * INTO parent FROM table WHERE table_id = new.parent_pointer;

    IF new.pulled_field IS NULL THEN new.pulled_field = parent.pulled_field; END IF;

    RETURN new;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
COMMENT ON FUNCTION table_pull_forward_from_parent() IS 'see vegbiendev.nceas.ucsb.edu/wiki/Pull-forward';