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

[Kevin Neufeld]

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);';
> 
>  END LOOP;
>  END LOOP;
>  END LOOP;
> 
> 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$

BEGIN
   EXECUTE
     '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)';
END
$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