CODEWITHSUNDEEP
postgresqlpostgisdatabase-trigger
jaksco
jaksco

Reputation: 531

PostgreSQL trigger execute procedure or join on NEW

-- trigger function
CREATE OR REPLACE FUNCTION fn_cities_geo_update_event() RETURNS trigger AS $fn_cities_geo_update_event$
  BEGIN

is it ok to set a value (geog) which will be used later in the function?

    NEW.geog := ST_SetSRID(ST_MakePoint(NEW.longitude,NEW.latitude), 4326)::geography;

this is one way that I tried to find the average of all cities within 90km of a new city so that the new city will have data populated about it

  if NEW.rent_avg IS null then
    NEW.rent_avg = (
      SELECT avg(a.rent_avg)
      FROM cities as a
-- I think I'm missing something here... ?
      ST_DWithin(a.geog, NEW.geog, 90000)
      );
  end if;

here is another way that I tried:

  if NEW.food_avg IS null then
  NEW.food_avg := (
    SELECT avg(a.food_avg)
    FROM    cities AS a
    JOIN    NEW AS b
      ON    ST_DWithin(a.geog, b.geog, 90000)
    );
  end if;
  RETURN NEW;
  END;
$fn_cities_geo_update_event$ LANGUAGE plpgsql;

but neither worked.

edit: here is a copy of the table that I'm working with

l    |u |n                         |population|unesco|r                        |c               |rent_avg|rent_low|rent_high|food_avg|food_low|food_high|transport_avg|transport_low|transport_high|k        |i  |quality|hcid                                 |hc  |latitude   |longitude |spread     |density   |distance  |dbn                       |state|geog                                              |id   
-----|--|--------------------------|----------|------|-------------------------|----------------|--------|--------|---------|--------|--------|---------|-------------|-------------|--------------|---------|---|-------|-------------------------------------|----|-----------|----------|-----------|----------|----------|--------------------------|-----|--------------------------------------------------|-----
false|NZ|Gisborne                  |     34274|     0|Australia and New Zealand|New Zealand     | 92.2430| 51.1720| 143.4150| 22.0300| 13.3190|  35.3490|       7.0650|       5.9800|       13.0450|4VHV8X00+|GIS|   1712|place:Gisborne                       |  46|-38.6640015|177.977005| 0.99940002|         0|          |                          |     |0101000020E6100000000000A0433F664000000000FE5443C0| 1611
true |NZ|Patutahi                  |       386|      |Australia and New Zealand|New Zealand     |        |        |         |        |        |         |             |             |              |4VHV9V00+|   |   1000|place:Patutahi                       |  35|-38.6170006|177.899994|           |          |       8.5|Patutahi                  |     |0101000020E6100000000000C0CC3C6640000000E0F94E43C0| 1624
true |NZ|Waihau Bay                |          |      |Australia and New Zealand|New Zealand     |        |        |         |        |        |         |             |             |              |4VJV8Q00+|   |   1000|place:Waihau_Bay                     |   6|-37.6780014|177.796005|           |          |110.699997|Waihau Bay                |     |0101000020E6100000000000E078396640000000C0C8D642C0| 1671
true |NZ|Tokomaru Bay              |       495|      |Australia and New Zealand|New Zealand     |        |        |         |        |        |         |             |             |              |4VHWV800+|   |   1000|place:Tokomaru_Bay                   |   5|-38.1329994|178.300003|           |          |65.4000015|Tokomaru Bay              |     |0101000020E6100000000000A09949664000000020061143C0| 1673
true |FR|Cornebarrieu              |          |      |Western Europe           |France          |        |        |         |        |        |         |             |             |              |8FM3M800+|   |   1000|place:Cornebarrieu                   | 112| 43.6559982|1.33299994| 3.60581994|          | 3.5999999|Cornebarrieu              |     |0101000020E6100000000000C0F753F53F000000C0F7D34540| 6070

edit: create trigger statement 

DROP TRIGGER IF EXISTS tr_cities_inserted ON cities;
CREATE TRIGGER tr_cities_inserted
  BEFORE INSERT ON cities
  FOR EACH ROW
  EXECUTE PROCEDURE fn_cities_geo_update_event();

Upvotes: 0

Views: 410

Answers (1)

Humpelstielzchen
Humpelstielzchen

Reputation: 6441

Okay, this is interesting.. I am new to triggers (as you may have noticed ^^) so it seems, when I recreate a table AFTER I created a trigger for that table, the trigger doesn't work. It just returns null for the respective columns.

When I stick to the proper order, I get it to work with my sample reproduction of your case:

CREATE TABLE so_postgres
(n text,
 r text,
 c text,
 rent_avg numeric,
 food_avg numeric,
 transport_avg numeric,
 latitude numeric,
 longitude numeric,
 geog geography
 );

 INSERT INTO so_postgres
 VALUES  ('Möhringen', 'central europe', 'germany', 200.45, 56.45, 4.56, 48.725866, 
           9.146131, ST_SetSRID(ST_Point(9.146131, 48.725866), 4326)),
         ('Vaihingen', 'central europe', 'germany', 155.33, 44.12, 2.78, 48.732550,
           9.108291, ST_SetSRID(ST_Point(9.108291, 48.732550), 4326)),
         ('Sigmaringen', 'central europe', 'germany', 298.11, 59.67, 1.99, 48.090797, 
           9.230243, ST_SetSRID(ST_Point(9.230243, 48.090797), 4326));

CREATE OR REPLACE FUNCTION fn_cities_geo_update_event() RETURNS trigger AS $fn_cities_geo_update_event$


BEGIN


NEW.geog := (ST_SetSRID(ST_MakePoint(NEW.longitude,NEW.latitude), 4326)::geography);

  if NEW.rent_avg IS null then
    NEW.rent_avg := (
      SELECT round(avg(a.rent_avg), 2)
      FROM so_postgres as a
      WHERE ST_DWithin(a.geog, NEW.geog, 50000)
      );
  end if;

 RETURN NEW;
  END;
$fn_cities_geo_update_event$ LANGUAGE plpgsql;


CREATE TRIGGER fn_cities_geo_update_event BEFORE INSERT OR UPDATE ON so_postgres FOR EACH ROW EXECUTE PROCEDURE fn_cities_geo_update_event();

 INSERT INTO so_postgres (n, r, c, latitude, longitude)
 VALUES ('Degerloch', 'central europe', 'germany', 48.725866, 9.146131); 

      n      |       r        |    c    | rent_avg | food_avg | transport_avg | latitude  | longitude |                        geog
-------------+----------------+---------+----------+----------+---------------+-----------+-----------+----------------------------------------------------
 Möhringen   | central europe | germany |   200.45 |    56.45 |          4.56 | 48.725866 |  9.146131 | 0101000020E610000012DDB3AED14A2240A1A3552DE95C4840
 Vaihingen   | central europe | germany |   155.33 |    44.12 |          2.78 | 48.732550 |  9.108291 | 0101000020E6100000FBE6FEEA71372240A857CA32C45D4840
 Sigmaringen | central europe | germany |   298.11 |    59.67 |          1.99 | 48.090797 |  9.230243 | 0101000020E61000000F441669E275224097C9703C9F0B4840
 Degerloch   | central europe | germany |   177.89 |          |               | 48.725866 |  9.146131 | 0101000020E610000012DDB3AED14A2240A1A3552DE95C4840

To answer your last comment: I add the ST_DWithin to the rest of the query via a WHERE-clause.

Does it work for you?

Upvotes: 1

Related Questions