<div dir="ltr">Hi,
Giuseppe,<div><br></div><div>The following is the error message.</div><div><br></div><div><span style="font-family:"Source Code Pro",SFMono-Regular,Menlo,Monaco,Consolas,"Liberation Mono","Courier New",monospace;font-size:12.95px;white-space:pre-wrap">NOTICE: identifier "SET style_description = CASE WHEN descriptiveterm ~* 'Polygon Closing Link' THEN 'Polygon Closing Line' ELSE 'Unclassified' END" will be truncated to "SET style_description = CASE WHEN descriptiveterm ~* 'Polygon C"
NOTICE: identifier "SET style_description = CASE WHEN descriptiveterm ~* 'Polygon Closing Link' THEN 'Polygon Closing Line' ELSE 'Unclassified' END" will be truncated to "SET style_description = CASE WHEN descriptiveterm ~* 'Polygon C"
LINE 11: psqlstring := 'UPDATE' || output || "SET style_description =...
^
ERROR: syntax error at or near "exec"
LINE 13: exec psqlstring;
^
SQL state: 42601
Character: 501</span> </div><div><br></div><div>Regards,</div><div><br></div><div>Shao <br></div></div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">On Wed, 22 Apr 2020 at 07:34, Shaozhong SHI <<a href="mailto:shishaozhong@gmail.com">shishaozhong@gmail.com</a>> wrote:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir="ltr">Hi, Giuseppe,<br><table cellpadding="0" style="border-collapse:collapse;margin-top:0px;width:auto;font-family:Roboto,RobotoDraft,Helvetica,Arial,sans-serif;font-size:14px;letter-spacing:0.2px;display:block"><tbody style="display:block"><tr style="height:auto;display:flex"><td style="white-space:nowrap;padding:0px;vertical-align:top;width:718.875px;line-height:20px;display:block;max-height:20px"></td></tr></tbody></table><div><br></div><div>See the following code to see what I am trying to do.</div><div><br></div><div>DO $$<br>DECLARE <br>wccdate TEXT;<br>output TEXT := 'public.topographic_line_buckinghamshire_milton_keynes_line';<br>psqlstring TEXT;<br><br>BEGIN<br><br>execute format('ALTER TABLE %s ADD style_description varchar(50)', output);<br>execute format('ALTER TABLE %s ADD style_code int2', output);<br>psqlstring := 'UPDATE' || output || "SET style_description = CASE WHEN descriptiveterm ~* 'Polygon Closing Link' THEN 'Polygon Closing Line' ELSE 'Unclassified' END";<br>psqlstring := psqlstring || "'," || " " || output || ")'";<br>execute psqlstring;<br><br><br>END $$;<br></div><div><br></div><div><br></div><div>The other day when I composed a very long string to do something else, it worked.</div><div><br></div><div>Now, I am trying to make update statement and then execute the string. But, I keep get error message saying the string gets truncated.</div><div><br></div><div>Regards,</div><div><br></div><div>Shao</div></div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">On Tue, 21 Apr 2020 at 23:09, Giuseppe Broccolo <<a href="mailto:g.broccolo.7@gmail.com" target="_blank">g.broccolo.7@gmail.com</a>> wrote:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir="ltr"><div>Hi Shao,</div><div><br></div><div>Maybe you are looking about how to pass SQL statements via a shell here-document:</div><div><pre>psql [options] <<EOF
SELECT *<br>FROM foo1<br>WHERE col='val';<br><br>SELECT * FROM foo2;<br>EOF<br></pre><pre><span style="font-family:arial,sans-serif">Eventual bash variable within the here-document can be interpolated. To avoid that just quote the first instance of <span style="font-family:monospace">EOF</span><br></span></pre><span style="font-family:monospace">psql [options] <<'EOF' <br></span></div><div><span style="font-family:monospace">SELECT *<br>FROM foo1<br>WHERE col='val';<br><br>SELECT * FROM foo2;</span></div><div><span style="font-family:monospace">EOF</span><br><br></div></div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">Il giorno mar 21 apr 2020 alle ore 21:16 Shaozhong SHI <<a href="mailto:shishaozhong@gmail.com" target="_blank">shishaozhong@gmail.com</a>> ha scritto:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir="ltr">It is quite appealing to wrap up a large block of psql codes as a string and execute the string.<div><br></div><div>And, how to deal with quotes within quotes.</div><div><br></div><div>I tried short text strings. It worked well, but it does not seem to work with very long strings in different lines.</div><div><br></div><div>Can anyone shed light on this?</div><div><br></div><div>Regards,</div><div><br></div><div>Shao</div></div>
_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><br>
<a href="https://lists.osgeo.org/mailman/listinfo/postgis-users" rel="noreferrer" target="_blank">https://lists.osgeo.org/mailman/listinfo/postgis-users</a></blockquote></div>
_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><br>
<a href="https://lists.osgeo.org/mailman/listinfo/postgis-users" rel="noreferrer" target="_blank">https://lists.osgeo.org/mailman/listinfo/postgis-users</a></blockquote></div>
</blockquote></div>