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

[Roxanne]

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