[GRASS-dev] Issue with db.execute: error altering a PostGIS table

[Markus Neteler]

Hi,

we have connected a mapset to PostGIS in G7.2 (Linux) and face the
issue that db.execute struggles with an error while psql does not:

The situation is that user ${TARGETDB_USER_TEST_ADMIN} has the
permission to see schema "test". This user is the only one who may
also generate and modify new tables in this schema.

Given this, db.execute doesn't fully work (I thought it would
basically send the SQL statement to the PostGIS server):

GRASS 7.2.1 (pg_utm_dev):~ > db.login user=${TARGETDB_USER_TEST_ADMIN}
host=${TARGETDB_HOST} port=${TARGETDB_PORT} --o
GRASS 7.2.1 (pg_utm_dev):~ > db.execute sql="ALTER TABLE
${SCHEMA}.${TABLE} ALTER COLUMN geom TYPE geometry USING
ST_SetSRID(geom,25832);"
DBMI-PostgreSQL driver error:
Unable to execute:
ALTER TABLE test.xyz ALTER COLUMN geom TYPE geometry USING
ST_SetSRID(geom,25832);
ERROR:  function st_setsrid(postgis.geometry, integer) does not exist
LINE 1: ...32n_parent1 ALTER COLUMN geom TYPE geometry USING ST_SetSRID...
                                                              ^
TIP:  No function matches the given name and argument types. You might
need to add explicit type casts.

However, doing the same (?) in psql it works:

# connect:
  psql -U ${TARGETDB_USER_TEST_ADMIN} \
  -h ${TARGETDB_HOST} -p ${TARGETDB_PORT} -d ${TARGETDB_NAME} \
  -v TABLE=${SCHEMA}.${TABLE} \
  -f ${PATH_SQL}/update_geom.sql

#  update geom:
ALTER TABLE :TABLE
ALTER COLUMN geom TYPE geometry
USING ST_SetSRID(geom,25832);

No error here.

What is the trick to do the same with db.execute?

Thanks,
Markus

PS: I wonder if this matters (but my knowledge about DB stuff is not huge :-)
https://gis.stackexchange.com/questions/132103/postgis-st-within-does-not-exist