PostgreSQL Mathematical Function

Question

I have a table aps_sections with many integer fields (such as bare_width and worn_width). I also have multiple look up tables (such as aps_bare_width and aps_worn_width) which contain an ID column and a WEIGHTING column. The ID is recorded in the above columns of the aps_sections table. I need to sum the WEIGHTINGs of the columns in the aps_sections table (whereby the WEIGHTING value comes from the look up tables). I have successfully managed this using the below SELECT statement.

SELECT aps_sections.ogc_fid,
       ( aps_bare_width.weighting
       + aps_worn_width.weighting
       + aps_gradient.weighting
       + aps_braiding.weighting
       + aps_pigeon.weighting
       + aps_depth.weighting
       + aps_standing_water.weighting
       + aps_running_water.weighting
       + aps_roughness.weighting
       + aps_surface.weighting
       + aps_dynamic.weighting
       + aps_ex_cond.weighting
       + aps_promotion.weighting
       + aps_level_of_use.weighting) AS calc
FROM row_access.aps_sections,
     row_access.aps_bare_width,
     row_access.aps_worn_width,
     row_access.aps_gradient,
     row_access.aps_braiding,
     row_access.aps_pigeon,
     row_access.aps_depth,
     row_access.aps_standing_water,
     row_access.aps_running_water,
     row_access.aps_roughness,
     row_access.aps_surface,
     row_access.aps_dynamic,
     row_access.aps_ex_cond,
     row_access.aps_promotion,
     row_access.aps_level_of_use
WHERE aps_bare_width.fid = aps_sections.bare_width
AND   aps_worn_width.fid = aps_sections.worn_width
AND   aps_gradient.fid = aps_sections.gradient
AND   aps_braiding.fid = aps_sections.braiding
AND   aps_pigeon.fid = aps_sections.pigeon
AND   aps_depth.fid = aps_sections.depth
AND   aps_standing_water.fid = aps_sections.standing_water
AND   aps_running_water.fid = aps_sections.running_water
AND   aps_roughness.fid = aps_sections.roughness
AND   aps_surface.fid = aps_sections.surface
AND   aps_dynamic.fid = aps_sections.dynamic
AND   aps_ex_cond.fid = aps_sections.ex_cond
AND   aps_promotion.fid = aps_sections.promotion
AND   aps_level_of_use.fid = aps_sections.level_of_use

What I now need to do is create a function that adds the calculated result to the physical_sn_priority column of the aps_sections table. My understanding so far is that my function should look similar to:

CREATE OR REPLACE FUNCTION row_access.aps_weightings()
  RETURNS trigger AS
$BODY$
    BEGIN
    NEW.physical_sn_priority := ;
       RETURN NEW;
    END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION public.update_km()
  OWNER TO postgres;

But I don't know what to put after NEW.physical_sn_priority :=. I am a beginner to SQL and to PostgreSQL so I would appreciate any guidance!

Answer 1

While Erwin is (as always) correct that a version would be helpful, I think your answer will be simplest with the SELECT ... INTO construction for PL/pgSQL. (Not the same as SELECT INTO that works like INSERT or CREATE TABLE.)

SELECT ( aps_bare_width.weighting
   + /* obvious deletia */
   + aps_level_of_use.weighting)
INTO NEW.physical_sn_priority
FROM row_access.aps_bare_width,
 /* snip */,
 row_access.aps_level_of_use
WHERE aps_bare_width.fid = NEW.bare_width
    AND /* snip */
    aps_level_of_use.fid = NEW.level_of_use;

RETURN NEW;

According to the documentation, the INTO can appear in several other places in the line; I find this simple to understand.

[EDIT]

While this works, on reflection, I think the schema should be revised.

CREATE TYPE weighted_item_t AS ENUM ('bare_width', /* ... */, 'level_of_use');
CREATE TABLE current_weights(item_type weighted_item_t, fid int, current_weight float);
     /* key and checks omitted */
     /* note, if item_type can be deduced from fid, we don't even need the enum */
CREATE TABLE sections_items(section_id int /* FK into aps_sections */,
     item_type weighted_item_t, fid int);

Now the queries are going to collapse into simple sums. You need to insert records into section_items before aps_sections, which can be done with deferred constraints in a transaction with or without a stored procedure, depending on how you acquire the data and how much control you have over its format. If (and this is not clear, because it won't change on updates) you want the denormalized total, you can get it with

SELECT SUM(current_weight) INTO NEW.physical_sn_priority
FROM section_items NATURAL JOIN current_weights
WHERE NEW.section_id=section_items.section_id;

This will work out much better if additional weighted characteristics are added at some future date.

Answer 2

Simplified test case

You should present your question with less noise. This shorter query does the job just fine:

SELECT aps_sections.ogc_fid,
       ( aps_bare_width.weighting
       + aps_worn_width.weighting
       + aps_gradient.weighting) AS calc
FROM row_access.aps_sections,
     row_access.aps_bare_width,
     row_access.aps_worn_width,
     row_access.aps_gradient,
WHERE aps_bare_width.fid = aps_sections.bare_width
AND   aps_worn_width.fid = aps_sections.worn_width
AND   aps_gradient.fid = aps_sections.gradient;

Answer

As @Andrew already advised, the golden way would be to simplify your schema.

If, for some reason, this is not possible, here is an alternative to simplify the addition of many columns (which may or may not be NULL), create this tiny, powerful function:

CREATE OR REPLACE FUNCTION f_sum(ANYARRAY)
  RETURNS numeric LANGUAGE sql AS
'SELECT sum(i)::numeric FROM unnest($1) i';

Call:

SELECT f_sum('{2,NULL,7}'::int[])

• It takes a polymorphic array type and returns numeric. Works for any number type that can be summed by sum(). Cast the result if needed.
• Simplifies the syntax for summing lots of columns.
• NULL values won't break your calculation because the aggregate function sum() ignores those.

In a trigger function this could be used like this:

NEW.physical_sn_priority := f_sum(ARRAY [
    COALESCE(physical_sn_priority, 0)     -- add to original value
   ,(SELECT weighting FROM aps_bare_width x WHERE x.fid = NEW.bare_width)
   ,(SELECT weighting FROM aps_worn_width x WHERE x.fid = NEW.worn_width)
   ,(SELECT weighting FROM aps_gradient   x WHERE x.fid = NEW.gradient)
    ...
   ])

Since all your joined tables are only good for looking up a single field, and completely independent from each other, you can just as well use individual subqueries. I also went this way, because we do not know whether any of the subqueries might return NULL, in which case your original query or Andrew's version would result in no row / no assignment.

Assignment to NEW really only makes sense in a BEFORE trigger on the table aps_sections. This code works BEFORE INSERT (where the row cannot be found in the table yet!) as well as BEFORE UPDATE. You want to use the current values from NEW, not the old row version in the table.