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 <[email protected]> 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 < > [email protected]> 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 <[email protected]> >> 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 < >>> [email protected]> 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 >>>> [email protected] >>>> https://lists.osgeo.org/mailman/listinfo/postgis-users >>> >>> _______________________________________________ >>> postgis-users mailing list >>> [email protected] >>> https://lists.osgeo.org/mailman/listinfo/postgis-users >> >> _______________________________________________ >> postgis-users mailing list >> [email protected] >> https://lists.osgeo.org/mailman/listinfo/postgis-users > > _______________________________________________ > postgis-users mailing list > [email protected] > https://lists.osgeo.org/mailman/listinfo/postgis-users
_______________________________________________ postgis-users mailing list [email protected] https://lists.osgeo.org/mailman/listinfo/postgis-users
