<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html; charset=ISO-8859-1"
http-equiv="Content-Type">
</head>
<body text="#000000" bgcolor="#ffffff">
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.<br>
<br>
Birgit.<br>
<br>
On 17.12.2009 16:38, David William Bitner wrote:
<blockquote
cite="mid:d2f53e190912170738m55a33048t6ecb215054567e18@mail.gmail.com"
type="cite">Birgit,
<div><br>
</div>
<div>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.</div>
<div><br>
</div>
<div>David<br>
<br>
<div class="gmail_quote">On Thu, Dec 17, 2009 at 9:27 AM, Birgit
Laggner <span dir="ltr"><<a moz-do-not-send="true"
href="mailto:birgit.laggner@vti.bund.de">birgit.laggner@vti.bund.de</a>></span>
wrote:<br>
<blockquote class="gmail_quote"
style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">Dear
list,<br>
<br>
I am trying to generalize a pl/pgsql function I have written (see<br>
below). I would like to define schema and table names, as well as<br>
certain column names, in the function call (as in the PostGIS function<br>
AddGeometryColumn) in order to use them to define schema and table names<br>
and everything else within the function queries.<br>
<br>
My problem is, that postgres doesn't recognize the defined variable<br>
names if I call them in a FROM clause or INSERT INTO. This is the error<br>
message:<br>
<br>
ERROR: Schema »schemaname« does not exist<br>
LINE 1: SELECT count( $1 ) from schemaname.table_a<br>
^<br>
QUERY: SELECT count( $1 ) from schemaname.table_a<br>
CONTEXT: PL/pgSQL function "_laggner_b_pgintersection" line 16 at<br>
assignment<br>
<br>
I can't imagine that it should be impossible to use variable schema and<br>
table names in a plpgsql function. So, if anybody has suggestions, I<br>
would be quite happy.<br>
<br>
Thanks and regards,<br>
<br>
Birgit.<br>
<br>
My PostGIS version: 1.4.0-10.1<br>
My PostgreSQL version: 8.4.1-2.1<br>
<br>
My pl/pgsql function:<br>
<br>
CREATE OR REPLACE FUNCTION _laggner_b_pgintersection(schemaname<br>
varchar(20), table_a varchar(50), a_id varchar(20), table_b varchar(50),<br>
b_id varchar(20), intersection varchar(60)) RETURNS void AS<br>
$BODY$<br>
<br>
DECLARE<br>
counter integer;<br>
recordset_object RECORD;<br>
i integer;<br>
n integer;<br>
<br>
BEGIN<br>
<br>
counter := 0;<br>
n := count(a_id) from schemaname.table_a;<br>
<br>
--1. Intersection:<br>
<br>
FOR i in 1..n LOOP<br>
<br>
RAISE NOTICE 'Beginn Intersection Tabelle 1, Polygon %', i;<br>
<br>
FOR recordset_object IN<br>
<br>
SELECT<br>
a.a_id ,<br>
b.b_id,<br>
ST_intersection(a.the_geom, b.the_geom) AS the_geom<br>
FROM schemaname.table_a a, schemaname.table_b b<br>
WHERE a.a_id=i and<br>
st_intersects(a.the_geom, b.the_geom) and<br>
a.the_geom && b.the_geom<br>
<br>
LOOP<br>
<br>
execute<br>
'INSERT INTO ''||schemaname||''.''||intersection||'' (''||a_id||'',<br>
''||b_id||'', the_geom) '||<br>
'VALUES ( '||<br>
''||recordset_object||'.''||a_id||'', '||<br>
''||recordset_object||'.''||b_id||'', '||<br>
''||recordset_object||'.the_geom);';<br>
/*<br>
alternatively:<br>
INSERT INTO schemaname.intersection (a_id, b_id, the_geom)<br>
VALUES (<br>
recordset_object.a_id,<br>
recordset_object.b_id,<br>
recordset_object.the_geom);<br>
*/<br>
counter := counter + 1;<br>
<br>
RAISE NOTICE 'Schreibe Intersection-Polygon %', counter ;<br>
<br>
END LOOP;<br>
<br>
counter := 0;<br>
<br>
END LOOP;<br>
<br>
END;<br>
$BODY$<br>
LANGUAGE 'plpgsql' VOLATILE;<br>
ALTER FUNCTION _laggner_b_pgintersection(schemaname varchar(20), table_a<br>
varchar(50), a_id varchar(20), table_b varchar(50), b_id varchar(20),<br>
intersection varchar(60)) OWNER TO postgres;<br>
<br>
_______________________________________________<br>
postgis-users mailing list<br>
<a moz-do-not-send="true"
href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a><br>
<a moz-do-not-send="true"
href="http://postgis.refractions.net/mailman/listinfo/postgis-users"
target="_blank">http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br>
</blockquote>
</div>
<br>
<br clear="all">
<br>
-- <br>
************************************<br>
David William Bitner<br>
</div>
<pre wrap="">
<fieldset class="mimeAttachmentHeader"></fieldset>
_______________________________________________
postgis-users mailing list
<a class="moz-txt-link-abbreviated" href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a>
<a class="moz-txt-link-freetext" href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</a>
</pre>
</blockquote>
</body>
</html>