[prev in list] [next in list] [prev in thread] [next in thread]
List: postgis-users
Subject: Re: [postgis-users] Can we treat a large block of psql codes as a string and execute the string?
From: Shaozhong SHI <shishaozhong () gmail ! com>
Date: 2020-04-26 14:44:20
Message-ID: CA+i5JwZaWJ_q99qv9qu_AZGKntACeaqT_fv_BQN9GwW+rrBgNg () mail ! gmail ! com
[Download RAW message or body]
[Attachment #2 (multipart/alternative)]
Thank you very much,
I will try that.
Regards,
Shao
On Fri, 24 Apr 2020 at 14:44, Roxanne <rox@tara-lu.com> wrote:
> Shao,
>
> Remember those $$ that start your clause are string escapes.
> You can use another form inside your block instead of the quotes
> such as
>
> psqlstring := 'UPDATE' || output || $b$SET style_description = CASE WHEN
> descriptiveterm ~* 'Polygon Closing Link' THEN 'Polygon Closing
> Line' ELSE 'Unclassified' END$b$;
>
> On 4/21/2020 11:34 PM, Shaozhong SHI wrote:
> > Hi, Giuseppe,
> >
> >
> > See the following code to see what I am trying to do.
> >
> > DO $$
> > DECLARE
> > wccdate TEXT;
> > output TEXT :=
> 'public.topographic_line_buckinghamshire_milton_keynes_line';
> > psqlstring TEXT;
> >
> > BEGIN
> >
> > execute format('ALTER TABLE %s ADD style_description varchar(50)',
> output);
> > execute format('ALTER TABLE %s ADD style_code int2', output);
> > psqlstring := 'UPDATE' || output || "SET style_description = CASE WHEN
> descriptiveterm ~* 'Polygon Closing Link' THEN 'Polygon Closing Line' ELSE
> 'Unclassified' END";
> > psqlstring := psqlstring || "'," || " " || output || ")'";
> > execute psqlstring;
> >
> >
> > END $$;
> >
> >
> > The other day when I composed a very long string to do something else,
> it worked.
> >
> > Now, I am trying to make update statement and then execute the string.
> But, I keep get error message saying the string gets truncated.
> >
> > Regards,
> >
> > Shao
> >
> > On Tue, 21 Apr 2020 at 23:09, Giuseppe Broccolo <g.broccolo.7@gmail.com
> <mailto:g.broccolo.7@gmail.com>> wrote:
> >
> > Hi Shao,
> >
> > Maybe you are looking about how to pass SQL statements via a shell
> here-document:
> >
> > psql [options] <<EOF
> > SELECT *
> > FROM foo1
> > WHERE col='val';
> >
> > SELECT * FROM foo2;
> > EOF
> >
> > Eventual bash variable within the here-document can be interpolated.
> To avoid that just quote the first instance of EOF
> >
> > psql [options] <<'EOF'
> > SELECT *
> > FROM foo1
> > WHERE col='val';
> >
> > SELECT * FROM foo2;
> > EOF
> >
> >
> > Il giorno mar 21 apr 2020 alle ore 21:16 Shaozhong SHI <
> shishaozhong@gmail.com <mailto:shishaozhong@gmail.com>> ha scritto:
> >
> > It is quite appealing to wrap up a large block of psql codes as
> a string and execute the string.
> >
> > And, how to deal with quotes within quotes.
> >
> > I tried short text strings. It worked well, but it does not
> seem to work with very long strings in different lines.
> >
> > Can anyone shed light on this?
> >
> > Regards,
> >
> > Shao
> > _______________________________________________
> > postgis-users mailing list
> > postgis-users@lists.osgeo.org <mailto:
> postgis-users@lists.osgeo.org>
> > https://lists.osgeo.org/mailman/listinfo/postgis-users
> >
> > _______________________________________________
> > postgis-users mailing list
> > postgis-users@lists.osgeo.org <mailto:postgis-users@lists.osgeo.org>
> > https://lists.osgeo.org/mailman/listinfo/postgis-users
> >
> >
> > _______________________________________________
> > postgis-users mailing list
> > postgis-users@lists.osgeo.org
> > https://lists.osgeo.org/mailman/listinfo/postgis-users
>
> _______________________________________________
> postgis-users mailing list
> postgis-users@lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-users
[Attachment #5 (text/html)]
<div dir="ltr">Thank you very much,<div><br></div><div>I will try \
that.</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 Fri, 24 Apr 2020 at 14:44, \
Roxanne <<a href="mailto:rox@tara-lu.com">rox@tara-lu.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">Shao,<br> <br>
Remember those $$ that start your clause are string escapes.<br>
You can use another form inside your block instead of the quotes<br>
such as<br>
<br>
psqlstring := 'UPDATE' || output || $b$SET style_description = CASE WHEN \
descriptiveterm ~* 'Polygon Closing Link' THEN 'Polygon Closing<br> \
Line' ELSE 'Unclassified' END$b$;<br> <br>
On 4/21/2020 11:34 PM, Shaozhong SHI wrote:<br>
> Hi, Giuseppe,<br>
><br>
><br>
> See the following code to see what I am trying to do.<br>
><br>
> 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>
><br>
><br>
> The other day when I composed a very long string to do something else, it \
worked.<br> ><br>
> Now, I am trying to make update statement and then execute the string. But, I \
keep get error message saying the string gets truncated.<br> ><br>
> Regards,<br>
><br>
> Shao<br>
><br>
> 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> \
<mailto:<a href="mailto:g.broccolo.7@gmail.com" \
target="_blank">g.broccolo.7@gmail.com</a>>> wrote:<br> ><br>
> Hi Shao,<br>
><br>
> Maybe you are looking about how to pass SQL statements via a shell \
here-document:<br> ><br>
> psql [options] <<EOF<br>
> SELECT *<br>
> FROM foo1<br>
> WHERE col='val';<br>
><br>
> SELECT * FROM foo2;<br>
> EOF<br>
><br>
> Eventual bash variable within the here-document can be interpolated. To \
avoid that just quote the first instance of EOF<br> ><br>
> psql [options] <<'EOF'<br>
> SELECT *<br>
> FROM foo1<br>
> WHERE col='val';<br>
><br>
> SELECT * FROM foo2;<br>
> EOF<br>
><br>
><br>
> Il giorno mar 21 apr 2020 alle ore 21:16 Shaozhong SHI <<a \
href="mailto:shishaozhong@gmail.com" target="_blank">shishaozhong@gmail.com</a> \
<mailto:<a href="mailto:shishaozhong@gmail.com" \
target="_blank">shishaozhong@gmail.com</a>>> ha scritto:<br> ><br>
> It is quite appealing to wrap up a large block of psql codes as a \
string and execute the string.<br> ><br>
> And, how to deal with quotes within quotes.<br>
><br>
> I tried short text strings. It worked well, but it does not seem \
to work with very long strings in different lines.<br> ><br>
> Can anyone shed light on this?<br>
><br>
> Regards,<br>
><br>
> Shao<br>
> _______________________________________________<br>
> postgis-users mailing list<br>
> <a href="mailto:postgis-users@lists.osgeo.org" \
target="_blank">postgis-users@lists.osgeo.org</a> <mailto:<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><br> \
><br> > _______________________________________________<br>
> postgis-users mailing list<br>
> <a href="mailto:postgis-users@lists.osgeo.org" \
target="_blank">postgis-users@lists.osgeo.org</a> <mailto:<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><br> \
><br> ><br>
> _______________________________________________<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><br> <br>
_______________________________________________<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>
[Attachment #6 (text/plain)]
_______________________________________________
postgis-users mailing list
postgis-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic