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

[Giuseppe Broccolo]

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> 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>
> 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
>
> _______________________________________________
> 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/9d27ce50/attachment.html>