From: Stefan Sylla <stefansylla@gmx.de>
To: pgsql-sql@postgresql.org
Subject: update column based on postgis query on anther table
Date: Tue, 16 Jul 2013 09:53:25 +0700
Message-ID: <51E4B5A5.4040805@gmx.de> (raw)
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: stefansylla@gmx.de
Subject: Re: update column based on postgis query on anther table
In-Reply-To: <51E4B5A5.4040805@gmx.de>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox