Dear list,
This might be a postgis-specific question, but I could not get access to
the postgis mailing list so I will have a try here as my problem might
be related to SQL:
I need to update a column of a table based on a postgis-query function
that involves another table as follows:
Assuming I have the following two tables:
/* 1) point layer */
CREATE TABLE test1_point ( id serial PRIMARY KEY, id_test1_poly integer);
SELECT AddGeometryColumn('test1_point','the_geom',32648,'POINT',2);
INSERT INTO test1_point values ( 1,1,GeomFromText('POINT(91770.4424465354 2296241.06858129)',32648));
/* 2) polygon layer*/
CREATE TABLE test1_poly ( id serial PRIMARY KEY);
SELECT AddGeometryColumn('test1_poly','the_geom',32648,'POLYGON',2);
INSERT INTO test1_poly values ( 22,GeomFromText('POLYGON((91755.2765951597
2296254.99925063,91787.7961588885 2296240.64800429,91757.7034700958
2296227.19771158,91755.2765951597 2296254.99925063))',32648));
/**/
And I create the following function to get the value 'id' from
'test1_poly' table:
/**/
create function test1_point_get_id_test1_poly(integer) returns integer as '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;'
language SQL returns null on null input;
/**/
This function works perfectly when I am using it manually like
/**/
select test1_point_get_id_test1_poly(1)
/**/
(returns '22', as the point from test1_point lies within the polygon of
test1_poly)
Now I want to use a trigger function to automatically update the column
'id_test1_poly' in tabel 'test1_point':
/**/
create or replace function test1_point_get_id_test1_poly() returns
trigger as $$ begin new.id_test1_poly=test1_point_get_id_test1_poly(new.id); return new; end;
$$
language plpgsql volatile;
-- create trigger for function:
create trigger test1_point_get_id_test1_poly after insert or update on test1_point for each row execute procedure
test1_point_get_id_test1_poly();
/**/
However, if I insert a new row into 'test1_point', the column
'id_test1_poly' remains empty, i.e. the function seems to return a null
value:
/**/
INSERT INTO test1_point (id,the_geom) values ( 2,GeomFromText('POINT(91759.2587143761 2296245.93565599)',32648));
select * from test1_point where id=2
--(returns: 2;;"0101000020887F000086AFB123F466F6405393C3F7DA844141")
Any ideas what is going wrong here? Thanks in advance for any help!
Stefan
