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 > <[email protected] <mailto:[email protected]>> 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 > [email protected] > <mailto:[email protected]> > http://postgis.refractions.net/mailman/listinfo/postgis-users > > > > > -- > ************************************ > David William Bitner > > > _______________________________________________ > postgis-users mailing list > [email protected] > http://postgis.refractions.net/mailman/listinfo/postgis-users >
_______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users
