[postgis-users] plpgsql - problem using variable schema and table names

Birgit Laggner birgit.laggner at vti.bund.de
Wed Jan 6 00:54:06 PST 2010

Hi Kevin,

thank you for your detailled answer! You're right: I don't really need
the loops in this function. I just hoped to have a bit more of control
in case of errors and regarding the progress of the running function.
But with my test data sets your function with the simple sql-statement
is almost twice as fast as the (non-dynamic, but working) function with
the loops. Therefore, I did choose for the version you suggested.

Unfortunately, I have 2 other functions (which, together with the
discussed and some other functions, should be executed by a calling
function one after another), where I can't do it without these loops. I
managed that they are running, but I had to use so many workarounds,
that I am quite sure, the functions are very ineffective.
The reason, why I need the loops in these functions, is that I want to
compute the Difference of 2 data sets. That means, one row of the result
data set should consist of the remaining polygon of table_a resulting
from cutting away all overlapping areas with any polygons of table_b.
And because st_difference only compares one polygon at a time, I
normally get 3 different result polygons when there are 3 overlappings
for one polygon. To solve this, I let the st_difference loop until every
overlapping polygon is considered, and take the result of each loop as
an input for the next loop.

If you (or anybody else!!!) are interested: I copied the
difference-function below. Perhaps you can find some optimization trick
to improve performance...

Thanks again and a happy new year!


Here comes the script:

CREATE OR REPLACE  FUNCTION _laggner_b_pgdifference_auto_a(schemaname
varchar(20), table_a varchar(50), a_id varchar(20), table_b varchar(50),
b_id varchar(20), diff_a varchar(60)) RETURNS void AS

  recordset_object1  RECORD;
  recordset_object2  RECORD;
  i                  integer;
  n                  integer;
  j                  integer;
  m                  integer;
  tablename1         character(4);
  sql_1 text;
  sql_2 text;
  sql_3 text;
  sql_5 text;
  r_1 record;
  r_2 record;
  r_aid record;

--4. Difference a:

 i := 0;
 n := 0;

 sql_1 := 'select gid from

 for r_1 in execute sql_1 loop

 n := n+1;

 end loop;
 tablename1 := 'tmp1';

 'create table
'||quote_ident(schemaname)||'.'||quote_ident(tablename1)||' (gid serial,
'||quote_ident(b_id)||' integer, the_geom geometry);';

 'create table tmp2 (aid integer, the_geom geometry);';

 FOR i in 1..n LOOP --LOOP 1

 RAISE NOTICE 'Beginn Difference für Tablle 1, Polygon % ', i;

 sql_5 := 'SELECT '||quote_ident(a_id)||' as a_id from
'||quote_ident(schemaname)||'.'||quote_ident(table_a)||' as a where

 FOR r_aid in execute sql_5 loop end loop;

 raise notice 'r_aid nach erster Zuweisung: % ', r_aid;

 sql_3 := 'SELECT the_geom as the_geom from
'||quote_ident(schemaname)||'.'||quote_ident(table_a)||' as a where

 FOR recordset_object1 in execute sql_3 loop end loop;

 raise notice 'recordset_object1 nach erster Zuweisung: % ',

 'truncate table '||quote_ident(schemaname)||'.tmp1;';
 'ALTER SEQUENCE '||quote_ident(schemaname)||'.tmp1_gid_seq RESTART WITH

 'INSERT INTO '||quote_ident(schemaname)||'.tmp1
  ('||quote_ident(b_id)||', the_geom)
 SELECT b.'||quote_ident(b_id)||', b.the_geom
  from '||quote_ident(schemaname)||'.'||quote_ident(table_a)||' a,
       '||quote_ident(schemaname)||'.'||quote_ident(table_b)||' b
  where a.gid='||i||' and
        st_relate(a.the_geom, b.the_geom, ''2********'');';
 m := 0;

 sql_2 := 'select '||quote_ident(b_id)||' from

 FOR r_2 in execute sql_2 loop

 m := m+1;

 end loop;

 RAISE NOTICE 'Anzahl Intersection-Polygone: % ', m;

 IF m > 0  THEN

 j := 0;

 FOR j in 1..m LOOP --LOOP 2

 SELECT st_difference(recordset_object1.the_geom, tmp1.the_geom) as the_geom
  FROM birgit.tmp1
  INTO recordset_object2
  WHERE tmp1.gid=j;

 SELECT recordset_object2.the_geom INTO recordset_object1.the_geom;

 RAISE NOTICE 'Intersection-Polygon % verarbeitet. ', j;

   WHEN  sqlstate 'XX000'
   THEN --do nothing


  IF st_isempty(recordset_object1.the_geom)='f' then

  INSERT INTO tmp2 (aid, the_geom)

  'INSERT INTO '||quote_ident(schemaname)||'.'||quote_ident(diff_a)||'
   from tmp2;';

  'truncate table tmp2;';

 RAISE NOTICE 'Tabelle 1, Difference-Polygon % ist fertig. ', i ;

 ELSE RAISE NOTICE 'Kein Difference berechnet. ';



 'drop table '||schemaname||'.tmp1;';

 'drop table tmp2;';

ALTER FUNCTION _laggner_b_pgdifference_auto_a(
 schemaname varchar(20),
 table_a varchar(50),
 a_id varchar(20),
 table_b varchar(50),
 b_id varchar(20),
 diff_a varchar(60))
OWNER TO postgres;

On 22.12.2009 19:46, Kevin Neufeld wrote:
> Hi Birgit,
> Hmmm, it looks like you have a few errors in your script.
> Comments inline.
> Birgit Laggner wrote:
>> Hi Kevin,
>> for the defining of n, your suggestions works perfectly well. But in the
>> next step, I try to assign a record as an intersection of 2 tables. I
>> tried to solve this with a nested loop. But the problem is, after
>> assigning the records for table a and b, I can't address certain fields
>> within the records anymore, which would be necessary for the
>> intersection loop.
>> By now, the part in question of my function looks like this (r_a, r_b
>> are declared as record, sql_a, sql_b are declared as text):
>>   sql_a := 'select gid, '||a_id||' as '||a_id||', the_geom from
>> '||quote_ident(schemaname)||'.'||quote_ident(table_a);
> I'm assuming, based on your original post, that 'a_id' is a parameter
> you wish to pass as a parameter to the function. You may want to
> quote_ident it as well to properly handle special characters, etc that
> may occur in the column name.
>>   raise debug '%', sql_a;
>>   raise notice '%', sql_a;
>>   sql_b := 'select gid, '||b_id||' as '||b_id||', the_geom from
>> '||quote_ident(schemaname)||'.'||quote_ident(table_b);
> Same as for sql_a.
>>   raise debug '%', sql_b;
>>  FOR r_a in execute sql_a LOOP
>>  FOR r_b in execute sql_b LOOP
>>  FOR recordset_object IN execute 'select
>>    '||quote_ident(r_a.a_id) ||',
>>    '||quote_ident(r_b.b_id) ||',
>>    ST_intersection('||quote_ident(r_a.the_geom)||',
>> '||quote_ident(r_b.the_geom)||') AS the_geom
>>   WHERE st_intersects('||quote_ident(r_a.the_geom)||',
>> '||quote_ident(r_b.the_geom)||') and
>>         '||quote_ident(r_a.the_geom)||' && '||quote_ident(r_b.the_geom)
> Ok.  Here r_a is referencing a field called a_id.  So unless you've
> declared the text variable a_id to be 'a_id' (or passed it as a
> parameter with the same name), you'll get the error you see below.  In
> the sql_a declaration above, change the select clause to 'select gid,
> ' || quote_ident(a_id) || ' AS a_id, the_geom from '...  Similar for
> sql_b.
> Also, you are dereferencing the geometry fields which would be very
> slow.  Your call to quote_ident(r_a.the_geom) is taking the geometry
> field in record r_a and casting the whole thing to text so it can be
> used in the textual execute statement.
>>  LOOP
>>   execute
>>   'INSERT INTO ''||schemaname||''.''||intersection||'' (''||a_id||'',
>> ''||b_id||'', the_geom) '||
>>    'VALUES ( '||
>>     ''||recordset_object||'.''||a_id||'', '||
>>     ''||recordset_object||'.''||b_id||'', '||
>>     ''||recordset_object||'.the_geom);';
>> And the error message, I get, is:
>> ERROR:  Record »r_a« hat no field »a_id«
>> Any ideas, how to handle this?
>> Thanks a lot and merry christmas,
>> Birgit.
> This seems waaaay too complicated and I don't think passing the
> geometry around like that will work for you.  If I may suggest, I
> think you could replace all three LOOPs with a single sql statement
> (logically, it looks like it should do the same thing):
> CREATE OR REPLACE  FUNCTION _laggner_b_pgintersection(
>   schemaname varchar(20),
>   table_a varchar(50),
>   a_id varchar(20),
>   table_b varchar(50),
>   b_id varchar(20),
>   intersection varchar(60)) RETURNS void AS
> $BODY$
>     'INSERT INTO ' ||
>        quote_ident(schemaname) || '.' || quote_ident(intersection) ||
>          '(a.' || quote_ident(a_id) || ', b.' || quote_ident(b_id)
> ||', ST_Intersection(a.the_geom, b.the_geom) ' ||
>     'FROM ' || quote_ident(schemaname) || '.' || quote_ident(table_a)
> || ' AS a, ' ||
>                quote_ident(schemaname) || '.' || quote_ident(table_b)
> || ' AS b ' ||
>     'WHERE ST_Intersects(a.the_geom, b.the_geom)';
> $BODY$
> The only thing that is quoted here are the schema, table, and column
> names.  The query is parsed once by the query planner and then
> executed.  We're not casting geometries to text and back into the
> database again.  Is there a particular reason you broke out query into
> 3 main LOOPs?
> I hope this helps.
> You too, have a very Merry Christmas.
> Cheers,
> Kevin
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users

More information about the postgis-users mailing list