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

[Birgit Laggner]

Yes, I have noticed that, but I don't know how to do that, especially at
defining my n (loop end point variable). I tried various versions (also
with execute), but without success.

Birgit.

On 17.12.2009 16:38, David William Bitner wrote:
> 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 <mailto: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
>     <mailto:postgis-users at postgis.refractions.net>
>     http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>
>
>
> -- 
> ************************************
> David William Bitner
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>   
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20091217/35915c5c/attachment.html>