<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns="http://www.w3.org/TR/REC-html40">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<meta name="Generator" content="Microsoft Word 12 (filtered medium)">
<style><!--
/* Font Definitions */
@font-face
{font-family:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
@font-face
{font-family:Tahoma;
panose-1:2 11 6 4 3 5 4 4 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0in;
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:"Times New Roman","serif";}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:purple;
text-decoration:underline;}
span.EmailStyle17
{mso-style-type:personal-reply;
font-family:"Calibri","sans-serif";
color:#1F497D;}
.MsoChpDefault
{mso-style-type:export-only;}
@page WordSection1
{size:8.5in 11.0in;
margin:1.0in 1.0in 1.0in 1.0in;}
div.WordSection1
{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]-->
</head>
<body lang="EN-US" link="blue" vlink="purple">
<div class="WordSection1">
<p class="MsoNormal"><span style="font-size:11.0pt">Remi –<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt">Thanks for your response.<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt">I tried to use</span><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D"> “</span>execute format('CREATE TABLE %I AS SELECT objectid, f_code, shape FROM %I ; ', updatedtablename,
tablename) ;” as you suggested. I got the following error<o:p></o:p></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal">ERROR: prepared statement "format" does not exist<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">********** Error **********<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">ERROR: prepared statement "format" does not exist<o:p></o:p></p>
<p class="MsoNormal">SQL state: 26000<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">Do I need to use a PREPARE statement to define “format” to be able to execute this syntax?
<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">Also, when I try to use <o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">“ EXECUTE 'SELECT topology.CreateTopology(%s,32648, 0.001, TRUE) ; ',clean_topo;<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">I get :<o:p></o:p></p>
<p class="MsoNormal">”<o:p></o:p></p>
<p class="MsoNormal">ERROR: syntax error at or near "'SELECT topology.CreateTopology(%s,32648, 0.001, TRUE) ; '"<o:p></o:p></p>
<p class="MsoNormal">LINE 1: EXECUTE 'SELECT topology.CreateTopology(%s,32648, 0.001,...<o:p></o:p></p>
<p class="MsoNormal"> ^<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">********** Error **********<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">ERROR: syntax error at or near "'SELECT topology.CreateTopology(%s,32648, 0.001, TRUE) ; '"<o:p></o:p></p>
<p class="MsoNormal">SQL state: 42601<o:p></o:p></p>
<p class="MsoNormal">Character: 13 <o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">Finally, when I try to perform the UPDATE using _q (declared as text) as follows:<o:p></o:p></p>
<p class="MsoNormal"> _q:=<o:p></o:p></p>
<p class="MsoNormal"> 'UPDATE fgcm.'||updatedtablename||' SET '||topo_shape||'::topogeometry<o:p></o:p></p>
<p class="MsoNormal"> = topology.toTopoGeom(ST_Transform('||r.shape||'::geometry,32648), '||cleantopo||', 1, 1.0)<o:p></o:p></p>
<p class="MsoNormal"> WHERE '||objectid||' = '||r.objectid||';';<o:p></o:p></p>
<p class="MsoNormal"> RAISE EXCEPTION 'here is the query to manually test : %',_q ;<o:p></o:p></p>
<p class="MsoNormal">I get:<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">ERROR: syntax error at or near "_q"<o:p></o:p></p>
<p class="MsoNormal">LINE 1: _q:=<o:p></o:p></p>
<p class="MsoNormal"> ^<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">********** Error **********<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">ERROR: syntax error at or near "_q"<o:p></o:p></p>
<p class="MsoNormal">SQL state: 42601<o:p></o:p></p>
<p class="MsoNormal">Character: 13<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">I also tried formulating it like this:<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal"> _q:=<o:p></o:p></p>
<p class="MsoNormal"> 'UPDATE fgcm.%s SET %s::topogeometry<o:p></o:p></p>
<p class="MsoNormal"> = topology.toTopoGeom(ST_Transform(%s,32648), %s, 1, 1.0)<o:p></o:p></p>
<p class="MsoNormal"> WHERE %s = %s,' updatedtablename, topo_shape, r.shape, cleantopo, objectid, r.objectid;<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">I left the explicit typecast to topogeometry in place, but tried to replace all others with simple strings.<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">The query executed 21 seconds then failed with:<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">********** Error **********<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">ERROR: syntax error at or near "_q"<o:p></o:p></p>
<p class="MsoNormal">SQL state: 42601<o:p></o:p></p>
<p class="MsoNormal">Character: 13<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">I am executing this through pgAdmin.<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">Any other suggestions would be appreciated.<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">Steve<o:p></o:p></p>
<div style="border:none;border-top:solid #B5C4DF 1.0pt;padding:3.0pt 0in 0in 0in">
<p class="MsoNormal"><b><span style="font-size:10.0pt;font-family:"Tahoma","sans-serif"">From:</span></b><span style="font-size:10.0pt;font-family:"Tahoma","sans-serif""> Rémi Cura [mailto:remi.cura@gmail.com]
<br>
<b>Sent:</b> Tuesday, February 17, 2015 4:56 AM<br>
<b>To:</b> PostGIS Users Discussion; Miller, Stephan<br>
<b>Subject:</b> Re: [postgis-users] operator is not unique: text || geometry<o:p></o:p></span></p>
</div>
<p class="MsoNormal"><o:p> </o:p></p>
<div>
<p class="MsoNormal">Hey Stephan,<o:p></o:p></p>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<p class="MsoNormal">I'm afraid I must say the coding style is not good and dangerous.<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal">And I know that the very nature of plpgsql langage (and doc) make it difficult to produce a nice function.<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<p class="MsoNormal">AS it is, it is very hard to read it, and any user of your function could potentially inject SQL via your function.<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<p class="MsoNormal">For instance, <o:p></o:p></p>
</div>
<div>
<p class="MsoNormal">----------<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif""> execute 'CREATE TABLE ' || updatedtablename || ' AS SELECT objectid, f_code, shape FROM ' || tablename;</span><o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif"">------------</span><o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif"">should become (safe, easier to read, easier to port)</span><o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif"">-----------</span><o:p></o:p></p>
</div>
<div>
<p class="MsoNormal">execute format('CREATE TABLE %I AS SELECT objectid, f_code, shape FROM %I ; ', updatedtablename,tablename) ;<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal">-----------<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<p class="MsoNormal">AS a rule of thumb, you should start asking yourself question when you abuse quoting, like ~'''~<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<p class="MsoNormal">For instance<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal">-------------<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal">execute 'SELECT topology.CreateTopology(''' || cleantopo || ''',32648, 0.000001, TRUE);';<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal">-------------<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal">could simply be replaced by<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal">---------------------<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal">PERFORM topology.CreateTopology(clean_topo,32648, 0.000001, TRUE) ; <o:p></o:p></p>
</div>
<div>
<p class="MsoNormal">---------------------<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal">or, if you really really want to use an EXECUTE :<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal">---------------------<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal">EXECUTE 'SELECT topology.CreateTopology(%s,32648, 0.000001, TRUE) ; ',clean_topo ) ; <o:p></o:p></p>
</div>
<div>
<p class="MsoNormal">---------------------<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<p class="MsoNormal">You might also use the type '<span style="font-size:9.5pt;font-family:"Courier New";color:black">regclass</span>' instead of the type text when the text shall always represent a table.<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal">This would automatically raise error if the table doesn't exist, and would be schema-qualification safe.<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<p class="MsoNormal">Now to the supposed-to-be-faulty part, you have a sql problem on top of plpgsql problem.<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal">I would recommend to always fabricate your sql statement, then test it manually, then execute it.<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal">for instance, you could declare <o:p></o:p></p>
</div>
<div>
<p class="MsoNormal">_q text;<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal">then you fabricate your query : <o:p></o:p></p>
</div>
<div>
<p class="MsoNormal">---------------<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal">_q := <o:p></o:p></p>
</div>
<div>
<div>
<p class="MsoNormal">'UPDATE fgcm.'||updatedtablename||' SET '||topo_shape||'::topogeometry<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"> = topology.toTopoGeom(ST_Transform('||r.shape||'::geometry,32648), '||cleantopo||', 1, 1.0)<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"> WHERE '||objectid||' = '||r.objectid||';';<o:p></o:p></p>
</div>
</div>
<div>
<p class="MsoNormal">RAISE EXCEPTION 'here is the query to manually test : %',_q ; <o:p></o:p></p>
</div>
<div>
<p class="MsoNormal">-------------------<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal">then you print _q and execute it manually to see if the sql syntax is correct :<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal">(copy past the query given at execution time, then try to execute it in pgadmin or psql to check that syntax is correct).<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<p class="MsoNormal">The problem in this query is that it doesn't respect the SQL UPDATE syntax :<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal">you should do something like :<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal">UPDATE your_table_name SET (-<b>list_of_columns_to_update</b>-) = (expression_matching_list_of_columns) WHERE ...<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<p class="MsoNormal">So you can see that "SET '||topo_shape||'::topogeometry" is not correct.<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal">It is the same for your WHERE part :<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal">"WHERE '||objectid||' = '||r.objectid||';';"<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<p class="MsoNormal">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.<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<p class="MsoNormal">Cheers,<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal">Rémi-C<o:p></o:p></p>
</div>
</div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
<div>
<p class="MsoNormal">2015-02-17 10:04 GMT+01:00 Sandro Santilli <<a href="mailto:strk@keybit.net" target="_blank">strk@keybit.net</a>>:<o:p></o:p></p>
<p class="MsoNormal">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>
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><o:p></o:p></p>
</div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
</div>
</body>
</html>