On 4/22/2020 11:15 AM, Shaozhong SHI wrote:
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.
raise notice psqlstring;
Regards,
Shao
On Wed, 22 Apr 2020 at 14:35, Giuseppe Broccolo
<[email protected] <mailto:[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] <mailto:[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] <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]
<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
