PostgreSQL – using group by sum as input to function for creating additional column in a view

I am new to PostgreSQL and this might not really be the best way to achieve my result, but here is what i am trying.

I have a table called meteo and another called areas. meteo has values of measured precipitation at different stations, and the stations are grouped into areas identified by ids.

areas has the same ids found in meteo and their positions/geometries.

meteo

station precipitation   area_id
1       40              1
2       60              1
3       50              2
4       30              2

areas

zone_id geom
1       "THE GEOMETRY"
2       "THE GEOMETRY"

I need to create a view that aggregate all stations from meteo that have the same id, join to areas by id and SUM all precipitation for those areas.

Other than this, I also need an additional column in my view that tells me the magnitude of the SUM precipitation in each area, based on a classification.

I can create the view without the extra magnitude column, and I sincerely do not know how to move on.

Here is how I create my view for now:

create view my_view (area_id, precipitation, geom)
as select area_id, precipitation, geom from
    (select area_id, sum(precipitation) precipitation
    from meteo
    group by area_id) as mt,
areas areas
where zone_id = mt.area_id;

Then, I have created a function called calculatePrecipitationMagnitude that I would like to use in my view to calculate the additional column I need.

create or replace function calculatePrecipitationMagnitude(precipitation float4)
    returns text as $$
    begin
        if abs(precipitation) >= 100 then
            return 'level3';
        elsif abs(precipitation) >= 50 AND abs(precipitation) < 100 then
            return 'level2';
        elsif abs(precipitation) < 50 then
            return 'level1';
        else
            return 'level0';
        end if;
    end;
    $$
    LANGUAGE PLPGSQL;

The function seems to work fine but I am stuck in how to use it inside my view declaration.

Final view would be something like:

my_view

area_id precipitaion    geom            magnitude
1       100             "THE GEOMETRY"  level3
2       80              "THE GEOMETRY"  level2

I am using PostgreSQL 10.

Answer

Considering both of your queries are working fine you can write your view definition as

create view my_view 
as select area_id, precipitation, geom, calculatePrecipitationMagnitude(precipitation) "magnitude" from
    (select area_id, sum(precipitation) precipitation
    from meteo
    group by area_id) as mt,
areas.geom
where areas.area_id = mt.area_id;

Advertisement

Answer