sql - PostgreSQL Mathematical Function -

i have table aps_sections many integer fields (such bare_width , worn_width). have multiple tables (such aps_bare_width , aps_worn_width) contain id column , weighting column. id recorded in above columns of aps_sections table. need sum weightings of columns in aps_sections table (whereby weighting value comes tables). have managed using 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) calc 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 aps_bare_width.fid = aps_sections.bare_width ,   aps_worn_width.fid = aps_sections.worn_width ,   aps_gradient.fid = aps_sections.gradient ,   aps_braiding.fid = aps_sections.braiding ,   aps_pigeon.fid = aps_sections.pigeon ,   aps_depth.fid = aps_sections.depth ,   aps_standing_water.fid = aps_sections.standing_water ,   aps_running_water.fid = aps_sections.running_water ,   aps_roughness.fid = aps_sections.roughness ,   aps_surface.fid = aps_sections.surface ,   aps_dynamic.fid = aps_sections.dynamic ,   aps_ex_cond.fid = aps_sections.ex_cond ,   aps_promotion.fid = aps_sections.promotion ,   aps_level_of_use.fid = aps_sections.level_of_use 

what need create function adds calculated result physical_sn_priority column of aps_sections table. understanding far function should similar to:

create or replace function row_access.aps_weightings()   returns trigger $body$     begin     new.physical_sn_priority := ;        return new;     end; $body$   language plpgsql volatile   cost 100; alter function public.update_km()   owner postgres; 

but don't know put after new.physical_sn_priority :=. beginner sql , postgresql appreciate guidance!

while erwin (as always) correct version helpful, think answer simplest select ... into construction pl/pgsql. (not same select into works insert or create table.)

select ( aps_bare_width.weighting    + /* obvious deletia */    + aps_level_of_use.weighting) new.physical_sn_priority row_access.aps_bare_width,  /* snip */,  row_access.aps_level_of_use aps_bare_width.fid = new.bare_width     , /* snip */     aps_level_of_use.fid = new.level_of_use;  return new; 

according documentation, into can appear in several other places in line; find simple understand.

[edit]

while works, on reflection, think schema should revised.

create type weighted_item_t enum ('bare_width', /* ... */, 'level_of_use'); create table current_weights(item_type weighted_item_t, fid int, current_weight float);      /* key , checks omitted */      /* note, if item_type can deduced fid, don't need enum */ create table sections_items(section_id int /* fk aps_sections */,      item_type weighted_item_t, fid int); 

now queries going collapse simple sums. need insert records section_items before aps_sections, can done deferred constraints in transaction or without stored procedure, depending on how acquire data , how control have on format. if (and not clear, because won't change on updates) want denormalized total, can with

select sum(current_weight) new.physical_sn_priority section_items natural join current_weights new.section_id=section_items.section_id; 

this work out better if additional weighted characteristics added @ future date.