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

[Kevin Neufeld]

Small typo ... it's not
RAISE DEBUG '%s', sql;
but
RAISE DEBUG '%', sql;

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