[OBM Admin] complex table connecting

[Bán Miklós]

Hi all,

Here I share a complex table connecting solution with an example:

I have 3 tables:

main
observation
projects

I have to upload data regularly to "main" and "observation" and these
tables should be connected consistently: Every "observation" should be
connected to a "main" line. (nest boxes and the yearly observations of
them)

In this example there is one more strict connection with the
"projects" table.

These three tables are connected with foreign keys.

The "observation" connected with "main" using its unique id the obm_id,
and "projects" connected to both using its short name column. 

My users don't know the real connection id. They uses an other
identifier which is forming a unique identifier together with the
project's name! Therefore I created a trigger function which query and
fill the real connection using the non unique identifier and the
project's name when they upload data to the "observation" table.

This is the trigger function:

DECLARE
 i integer;
 p character varying(32);
 pid character varying(8);
 query text;
BEGIN
    IF tg_op = 'INSERT' THEN
        IF new.nestbox_project_id!='' THEN
             SELECT
array_to_string(regexp_matches(new.nestbox_project_id, '^(.+):',
'g'),'') INTO p; SELECT
array_to_string(regexp_matches(new.nestbox_project_id, ':(.+)$',
'g'),'') INTO pid; IF p IS NULL THEN p := new.nestbox_project; END IF;
             IF pid IS NULL THEN
                 pid := new.nestbox_project_id;
             END IF;
             query := 'SELECT obm_id FROM public_nestbox_data WHERE
project_id=' || quote_literal(pid) || 'AND project=' ||
quote_literal(p); EXECUTE query INTO i;

             new.nestbox_id=i;
             new.nestbox_project=p;
        END IF;
        RETURN new;
    END IF;
    IF tg_op = 'UPDATE' THEN
        IF new.nestbox_project_id!='' THEN
             SELECT
             array_to_string(regexp_matches(new.nestbox_project_id,
             '^(.+):', 'g'),'') INTO p; SELECT
             array_to_string(regexp_matches(new.nestbox_project_id,
             ':(.+)$', 'g'),'') INTO pid; IF p IS NULL THEN p :=
             new.nestbox_project; END IF; IF pid IS NULL THEN
                 pid := new.nestbox_project_id;
             END IF;
             query := 'SELECT obm_id FROM public_nestbox_data WHERE
             project_id=' || quote_literal(pid) || 'AND project=' ||
             quote_literal(p); EXECUTE query INTO i;
              
             new.nestbox_id=i;
             new.nestbox_project=p;
        END IF;
        RETURN new;
    END IF;
END

In the observation table I created a special column for this trigger
function.

The function has two modes:
- If the trigger column contains a name:value pair it will split
  this string and query the unique id (obm_id) based on the project's
  name and and the non unique id
- If the trigger column only contains a singe value it will query the
  corresponding obm_id using the project_name column and this non
  unique id. 

Using this trigger we can use both the web and and the file form. In the
web form there is a select list with the projects' names so the users
only need to fill the non-unique id. In the file form users should fill
the project name column and the non-unique id column as well.

I used an additional trick in the observation form setting. The project
column (which is a foreign key and used in the trigger function) is a
special list type:
It is a list and the list definition is the following:
SELECT:public_nestbox_data_projects.name

It is similar to the autocomplete list generation, generates a list of
select options.

Best wishes, Miki



-- 
Miklós Bán, PhD
MTA-DE "Lendület" Behavioural Ecology Research Group
Department of Evolutionary Zoology, University of Debrecen
H-4010 Debrecen, Egyetem tér 1.
Phone:  +36 52 512-900 ext. 62356
http://zoology.unideb.hu/?m=Miklos_Ban
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~