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

[Kevin Neufeld]

http://www.postgresql.org/docs/8.4/static/plpgsql-control-structures.html#PLPGSQL-RECORDS-ITERATING

Try:

FOR target IN EXECUTE text_expression LOOP
    ...
END LOOP;



I usually do something like this:

DECLARE
   ...
   schemaname text := 'myschema';
   tablename text := 'mytable';
   sql text;
   r record;

BEGIN;
   sql := 'SELECT ... FROM ' || quote_ident(schemaname) || '.' || quote_ident(tablename);
   RAISE DEBUG '%s', sql;

   FOR r IN EXECUTE sql LOOP
     ...
   END LOOP
END;

-- Kevin

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