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

[Stephen Woodbridge]

On 4/22/2020 11:15 AM, Shaozhong SHI wrote:
> Hi, Giuseppe,
>
> Thank you.
>
> As I remembered that I did a very long string in a loop and it worked.
>
> By the way, what is quickest way to print string on screen.  That 
> could be a much better way of checking.

raise notice psqlstring;

>
> Regards,
>
> Shao
>
> On Wed, 22 Apr 2020 at 14:35, Giuseppe Broccolo 
> <g.broccolo.7 at gmail.com <mailto:g.broccolo.7 at gmail.com>> wrote:
>
>     Hi Shao,
>
>     Looks like there's a syntax error in the UPDATE command that makes
>     truncate the string, I would say some missing spaces in the
>     concatenate. For instance,
>
>     [...] 'UPDATE' || output || "SET style_description [...]
>
>     should be
>
>     [...] 'UPDATE ' || output || " SET style_description [...]
>
>     Hope this helps,
>     Giuseppe.
>
>
>     Il giorno mer 22 apr 2020 alle ore 07:35 Shaozhong SHI
>     <shishaozhong at gmail.com <mailto:shishaozhong at gmail.com>> ha scritto:
>
>         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
>         <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