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

[Shaozhong SHI]

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.

Regards,

Shao

On Wed, 22 Apr 2020 at 14:35, Giuseppe Broccolo <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> 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
>
> _______________________________________________
> 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/b15b86ce/attachment.html>