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

[Birgit Laggner]

Dear list,

I am trying to generalize a pl/pgsql function I have written (see
below). I would like to define schema and table names, as well as
certain column names, in the function call (as in the PostGIS function
AddGeometryColumn) in order to use them to define schema and table names
and everything else within the function queries.

My problem is, that postgres doesn't recognize the defined variable
names if I call them in a FROM clause or INSERT INTO.  This is the error
message:

ERROR:  Schema »schemaname« does not exist
LINE 1: SELECT  count( $1 ) from schemaname.table_a
                                 ^
QUERY:  SELECT  count( $1 ) from schemaname.table_a
CONTEXT:  PL/pgSQL function "_laggner_b_pgintersection" line 16 at
assignment

I can't imagine that it should be impossible to use variable schema and
table names in a plpgsql function. So, if anybody has suggestions, I
would be quite happy.

Thanks and regards,

Birgit.

My PostGIS version: 1.4.0-10.1
My PostgreSQL version: 8.4.1-2.1

My pl/pgsql function:

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$

 DECLARE
  counter           integer;
  recordset_object  RECORD;
  i                 integer;
  n                 integer;

 BEGIN

  counter := 0;
  n := count(a_id) from schemaname.table_a;

--1. Intersection:

 FOR i in 1..n LOOP

 RAISE NOTICE 'Beginn Intersection Tabelle 1, Polygon %', i;
 
 FOR recordset_object IN
 
  SELECT
   a.a_id ,
   b.b_id,
   ST_intersection(a.the_geom, b.the_geom) AS the_geom
  FROM schemaname.table_a a, schemaname.table_b b
  WHERE a.a_id=i and
        st_intersects(a.the_geom, b.the_geom) and
        a.the_geom && b.the_geom

 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);';
/*
alternatively:
  INSERT INTO schemaname.intersection (a_id, b_id, the_geom)
   VALUES (
    recordset_object.a_id,
    recordset_object.b_id,
    recordset_object.the_geom);
*/            
 counter := counter + 1;

 RAISE NOTICE 'Schreibe Intersection-Polygon %', counter ;

 END LOOP;

 counter := 0;

 END LOOP;

 END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
ALTER 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)) OWNER TO postgres;