Hi, Giuseppe, I am following these examples.
https://stackoverflow.com/questions/12780275/dynamic-sql-query-in-postgres But, it appears that it does not allow long text strings. I wonder how to go about. Regards, Shao On Wed, 22 Apr 2020 at 07:47, Shaozhong SHI <shishaozh...@gmail.com> wrote: > Hi, Giuseppe, > > The following is the error message. > > NOTICE: identifier "SET style_description = CASE WHEN descriptiveterm ~* > 'Polygon Closing Link' THEN 'Polygon Closing Line' ELSE 'Unclassified' END" > will be truncated to "SET style_description = CASE WHEN descriptiveterm ~* > 'Polygon C" NOTICE: identifier "SET style_description = CASE WHEN > descriptiveterm ~* 'Polygon Closing Link' THEN 'Polygon Closing Line' ELSE > 'Unclassified' END" will be truncated to "SET style_description = CASE WHEN > descriptiveterm ~* 'Polygon C" LINE 11: psqlstring := 'UPDATE' || output || > "SET style_description =... ^ ERROR: syntax error at or near "exec" LINE > 13: exec psqlstring; ^ SQL state: 42601 Character: 501 > > Regards, > > Shao > > On Wed, 22 Apr 2020 at 07:34, Shaozhong SHI <shishaozh...@gmail.com> > 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.broccol...@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 < >>> shishaozh...@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@lists.osgeo.org >>>> https://lists.osgeo.org/mailman/listinfo/postgis-users >>> >>> _______________________________________________ >>> postgis-users mailing list >>> postgis-users@lists.osgeo.org >>> https://lists.osgeo.org/mailman/listinfo/postgis-users >> >>
_______________________________________________ postgis-users mailing list postgis-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/postgis-users
