Re: update column based on postgis query on anther table

> -----Original Message-----
> From: pgsql-sql-owner(at)postgresql(dot)org [mailto:pgsql-sql-
> owner(at)postgresql(dot)org] On Behalf Of ssylla
> Sent: Tuesday, July 16, 2013 3:58 AM
> To: pgsql-sql(at)postgresql(dot)org
> Subject: Re: [SQL] update column based on postgis query on anther table
>
> Hi Tom,
>
> I tried changing the trigger to be BEFORE instead of AFTER:
>
> create trigger test1_point_get_id_test1_poly
> before insert or update on test1_point for each row execute procedure
> test1_point_get_id_test1_poly();
>
> But the problem persits, the column id_test1_poly remains empty.
>
> Stefan
>
>

Stefan,

Probably, you need to change something in your trigger logic, because like Tom stated it's too late to change NEW values in AFTER record, and in BEFORE trigger the record with NEW value doesn't exist yet, so:

select test1_poly.id
from test1_poly,test1_point
where ST_Within(
test1_point.the_geom,
test1_poly.the_geom)
and test1_point.id=$1;

with $1 being NEW.id

returns NULL (record with test1_point.id = NEW.id isn't written yet into test1_point table).

May be this trigger function is what you need:

create or replace function test1_point_get_id_test1_poly() returns trigger as $$
begin
select test1_poly.id INTO new.id_test1_poly
from test1_poly
where ST_Within(
NEW.the_geom,
test1_poly.the_geom);
return new;
end;
$$
language plpgsql volatile;

Still there is an issue.
What if your point falls within multiple polygons (multiple records in test1_poly satisfy your criteria)?
In this case, select from test1_poly should return multiple records. This will break trigger function code.

Regards,
Igor Neyman