Thank you very much, I will try that.
Regards, Shao On Fri, 24 Apr 2020 at 14:44, Roxanne <[email protected]> wrote: > Shao, > > Remember those $$ that start your clause are string escapes. > You can use another form inside your block instead of the quotes > such as > > psqlstring := 'UPDATE' || output || $b$SET style_description = CASE WHEN > descriptiveterm ~* 'Polygon Closing Link' THEN 'Polygon Closing > Line' ELSE 'Unclassified' END$b$; > > On 4/21/2020 11:34 PM, Shaozhong SHI 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 <[email protected] > <mailto:[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] <mailto:[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] <mailto: > [email protected]> > > https://lists.osgeo.org/mailman/listinfo/postgis-users > > > > _______________________________________________ > > postgis-users mailing list > > [email protected] <mailto:[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
