Message-ID: <51E4B5A5.4040805@gmx.de>
Date: Tue, 16 Jul 2013 09:53:25 +0700
From: Stefan Sylla <stefansylla@gmx.de>
User-Agent: Mozilla/5.0 (X11; Linux i686;
 rv:17.0) Gecko/20130623 Thunderbird/17.0.7
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: update column based on postgis query on anther table
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-sql-owner@postgresql.org

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


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql