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

[David William Bitner]

Birgit,

The problem may be that you are creating a varchar variable for your schema
name and then you are trying to use it in an instance that is expecting a
database object. Anytime you are trying to insert variables as database
objects, you need to construct your query as a string and use execute
similar as to how you are creating your insert statement.

David

On Thu, Dec 17, 2009 at 9:27 AM, Birgit Laggner
<birgit.laggner at vti.bund.de>wrote:

> 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;
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>



-- 
************************************
David William Bitner
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20091217/488980d8/attachment.html>