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

Question

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.

You are complicating the things. I feel function is not required at all. please add the table structure and sample data with sample output of view. it will give us a better understanding — Akhilesh Mishra, Aug 17 '20 at 09:47
in your function from where you are getting `inc`. it will throw error — Akhilesh Mishra, Aug 17 '20 at 09:56
There is no need to use a function for this, a simple `CASE WHEN` should suffice: `CASE WHEN ABS(SUM(precipitation)) >= 100 THEN 'level3' WHEN...` etc — Ruben Helsloot, Aug 17 '20 at 09:57
Well, at least to me it improves readibility. If I want I can simply jump to my function definition and change it as needed instead of finding the CASE part in my query. Would using a function be discouraged in these cases? — umbe1987, Aug 17 '20 at 10:01

score 1 · Accepted Answer · answered Aug 17 '20 at 09:52

1

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;

answered Aug 17 '20 at 09:52

Akhilesh Mishra

5,876
3
16
32

thanks! that was simple, I tried many combination but I probably missed the alias "magnitude" to get it work. – umbe1987 Aug 17 '20 at 09:58

score 1 · Answer 2 · answered Aug 17 '20 at 10:36

1

I don't see how your current view is working. Two obvious errors are:

area.geom is undefined (or does not refer to the area table.
area_id is ambiguous in the select.

For your problem, I would suggest:

create view my_view as
    select m.area_id, m.precipitation, a.geom,
           calculatePrecipitationMagnitude(m.precipitation float4) as magnitude
    from (select area_id, sum(precipitation) as precipitation
          from meteo
          group by area_id
         ) m join
         areas a
         on a.area_id = m.area_id;

Notes:

The second table should be areas, not areas.geom.
All columns should be qualified in a query that references multiple tables. In your version, area_id in the select is ambiguous.
You don't need to list the column names for a view, if they are defined in the select. I prefer defining them in the select -- easier to maintain.

answered Aug 17 '20 at 10:36

Gordon Linoff

1,242,037
58
646
786

You are right but mine were not the exact table names and columns I have. I have used just random names because I did not want to use the real tables I am working on. Will update my question to make it work if anybody else is interested in a similar situation. – umbe1987 Aug 17 '20 at 10:55
Corrected, now it should work. Anyway, thanks for the notes, especially the last one. It really helped me clarifying some mistakes and best practices. – umbe1987 Aug 17 '20 at 12:11
I know it's late, but if you could still answer, what would be the way not to list the column names for my view when there is `calculatePrecipitationMagnitude(m.precipitation float4) as magnitude` that is calculated "outside" the subquery? – umbe1987 Sep 11 '20 at 12:33
got it: `select m.*, a.geom, calculatePrecipitationMagnitude(m.precipitation float4) as magnitude` – umbe1987 Sep 11 '20 at 12:45

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

2 Answers2