build_sightings() – Rebuild the SIGHTINGS table

Synopsis

build_sightings() INT
build_sightings(animid TEXT) INT

Input

animid

A BIOGRAPHY_DATA.AnimID value, designating the individual which is to have its SIGHTINGS rows recomputed.

This value is not validated. The BIOGRAPHY_DATA.AnimID need not exist; the function will execute without error.

The function’s return value may be examined to determine whether changes were made to SIGHTINGS.

Description

Rebuild the SIGHTINGS table’s rows, either all of them or those of only one individual.

When this function is called without an argument, it re-computes the entire SIGHTINGS table.

When called with a BIOGRAPHY_DATA.AnimID value, it re-computes the SIGHTINGS rows belonging to the given individual.

Rows may be created in SIGHTINGS for any day the individual may be alive, from earliest possible date of birth through the last day they were observed. This means, the database is examined for an individual’s presence from the individual’s BIOGRAPHY_DATA.BDMin through their BIOGRAPHY_DATA.DepartDate, inclusive.

The SIGHTING_CONTROLS table controls which tables are examined to see if an individual was sighted, the order in which they are examined, and the criteria that need to be met in order to be added to the SIGHTINGS table. The matching criteria in the rows of SIGHTING_CONTROLS are applied, in SIGHTING_CONTROLS.Step order, until the given individual is found matching the criteria. At that point a row for the individual is added to SIGHTINGS and the table(s) and criteria matched is recorded in the SIGHTINGS.Step column.

Community Membership

The SIGHTINGS.CommID column is given the value of the community id associated with the database row matched.

Per SIGHTING_CONTROLS.Source value, this is how the community value is obtained:

COMM_MEMBS

The COMM_MEMBS.CommID value is used.

NON_BREC_SIGHTING_SOURCES

The NON_BREC_SIGHTING_SOURCES.CommID value is used.

ROLES

The community value used, if SIGHTING_CONTROLS.Behavior is non-NULL and if such a value exists, is the value contained in the table holding detail on the event. For example, aggression events, which have a AGG EVENTS.Behavior value, are related to the AGGRESSIONS table. The AGGRESSIONS table has an AGGRESSIONS.CommID column. The value of the AGGRESSIONS.CommID column is used for aggression events when an aggression event is found and SIGHTING_CONTROLS.Behavior is AGG.

When SIGHTING_CONTROLS.Behavior is NULL, or when there is no community value in the detail associated with a behavior, the WATCHES.CommID value is used.

The “detail” tables with their own CommID column are:

Behavior code	Table
AGG	AGGRESSIONS
PAPER	LOCATIONS_PAPER
UTM	LOCATIONS_UTM
MATE	MATINGS
PG	PANTGRUNTS

SWELLING_SOURCES

The COMM_MEMBS.CommID value is used – if a row exists on COMM_MEMBS for the given individual on the given date.

If no such row exists on COMM_MEMBS, the special COMM_IDS community value of Unknown is used.

Examples

Rebuild SIGHTINGS for the individual with the AnimID of “JANE”

SELECT build_sightings('JANE');

Rebuild the entire SIGHTINGS table

SELECT build_sightings();

Return Value

The function returns the number of rows computed, regardless of how many rows previously existed in SIGHTINGS.

Page last generated: 2026-08-05 22:49:54 UTC