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

[Shaozhong SHI]

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>
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> 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
>> 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/20200422/5c19e1b6/attachment.html>