<div dir="ltr">Hey Stephan,<div><br></div><div>I'm afraid I must say the coding style is not good and dangerous.</div><div>And I know that the very nature of plpgsql langage (and doc) make it difficult to produce a nice function.</div><div><br></div><div>AS it is, it is very hard to read it, and any user of your function could potentially inject SQL via your function.</div><div><br></div><div>For instance, </div><div>----------</div><div><span style="font-family:Calibri,sans-serif;font-size:14.6666669845581px"> execute 'CREATE TABLE ' || updatedtablename || ' AS SELECT objectid, f_code, shape FROM ' || tablename;</span><br></div><div><span style="font-family:Calibri,sans-serif;font-size:14.6666669845581px">------------</span></div><div><span style="font-family:Calibri,sans-serif;font-size:14.6666669845581px">should become (safe, easier to read, easier to port)</span></div><div><span style="font-family:Calibri,sans-serif;font-size:14.6666669845581px">-----------</span></div><div>execute format('CREATE TABLE %I AS SELECT objectid, f_code, shape FROM %I ; ', updatedtablename,tablename) ;<br></div><div>-----------</div><div><br></div><div>AS a rule of thumb, you should start asking yourself question when you abuse quoting, like ~'''~</div><div><br></div><div>For instance</div><div>-------------</div><div>execute 'SELECT topology.CreateTopology(''' || cleantopo || ''',32648, 0.000001, TRUE);';<br></div><div>-------------</div><div>could simply be replaced by</div><div>---------------------</div><div>PERFORM topology.CreateTopology(clean_topo,32648, 0.000001, TRUE) ; </div><div>---------------------</div><div>or, if you really really want to use an EXECUTE :</div><div>---------------------</div><div>EXECUTE 'SELECT topology.CreateTopology(%s,32648, 0.000001, TRUE) ; ',clean_topo ) ; </div><div>---------------------</div><div><br></div><div>You might also use the type '<span style="color:rgb(0,0,0);font-family:monospace;font-size:12.8439998626709px;line-height:18.2399997711182px">regclass</span>' instead of the type text when the text shall always represent a table.</div><div>This would automatically raise error if the table doesn't exist, and would be schema-qualification safe.</div><div><br></div><div><br></div><div>Now to the supposed-to-be-faulty part, you have a sql problem on top of plpgsql problem.</div><div>I would recommend to always fabricate your sql statement, then test it manually, then execute it.</div><div>for instance, you could declare </div><div>_q text;</div><div>then you fabricate your query : </div><div>---------------</div><div>_q := </div><div><div>'UPDATE fgcm.'||updatedtablename||' SET '||topo_shape||'::topogeometry</div><div> = topology.toTopoGeom(ST_Transform('||r.shape||'::geometry,32648), '||cleantopo||', 1, 1.0)</div><div> WHERE '||objectid||' = '||r.objectid||';';</div></div><div>RAISE EXCEPTION 'here is the query to manually test : %',_q ; </div><div>-------------------</div><div>then you print _q and execute it manually to see if the sql syntax is correct :</div><div>(copy past the query given at execution time, then try to execute it in pgadmin or psql to check that syntax is correct).</div><div><br></div><div>The problem in this query is that it doesn't respect the SQL UPDATE syntax :</div><div>you should do something like :</div><div>UPDATE your_table_name SET (-<b>list_of_columns_to_update</b>-) = (expression_matching_list_of_columns) WHERE ...</div><div><br></div><div>So you can see that "SET '||topo_shape||'::topogeometry" is not correct.</div><div>It is the same for your WHERE part :</div><div>"WHERE '||objectid||' = '||r.objectid||';';"</div><div><br></div><div>Now it could be a feature of your code (like storing the name of columns to use in another table), I don't understand it sufficiently to say so.</div><div><br></div><div>Cheers,</div><div>Rémi-C</div></div><div class="gmail_extra"><br><div class="gmail_quote">2015-02-17 10:04 GMT+01:00 Sandro Santilli <span dir="ltr"><<a href="mailto:strk@keybit.net" target="_blank">strk@keybit.net</a>></span>:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><span class="">On Mon, Feb 16, 2015 at 10:02:14PM +0000, Miller, Stephan wrote:<br>
<br>
> I highlighted where I think the error is occurring in red below.<br>
<br>
</span>Please, *please*, don't use colors to highlight texts.<br>
I know we're in 2015 and the internet is full of colorful moving puppets,<br>
but if we stick to low tech it's easier for everyone to partecipate.<br>
<br>
Personally, I don't see colors in emails.<br>
<br>
--strk;<br>
_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a><br>
<a href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users" target="_blank">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a><br>
</blockquote></div><br></div>