[postgis-users] Can we treat a large block of psql codes as a string and execute the string?

[Shaozhong SHI]

Thank you very much,

I will try that.

Regards,

Shao

On Fri, 24 Apr 2020 at 14:44, Roxanne <rox at 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 at gmail.com
> <mailto:g.broccolo.7 at 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 at gmail.com <mailto:shishaozhong at 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 at lists.osgeo.org <mailto:
> postgis-users at lists.osgeo.org>
> >         https://lists.osgeo.org/mailman/listinfo/postgis-users
> >
> >     _______________________________________________
> >     postgis-users mailing list
> >     postgis-users at lists.osgeo.org <mailto:postgis-users at lists.osgeo.org>
> >     https://lists.osgeo.org/mailman/listinfo/postgis-users
> >
> >
> > _______________________________________________
> > postgis-users mailing list
> > postgis-users at lists.osgeo.org
> > https://lists.osgeo.org/mailman/listinfo/postgis-users
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-users
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20200426/31488075/attachment.html>