[postgis-users] ST_X does not exist in an update statement

[Giuseppe Broccolo]

Hi Denis,

2017-07-18 11:51 GMT+02:00 Denis Rouzaud <denis.rouzaud at gmail.com>:

> Hi all,
>
> I'm bumbing my head against the wall.
>
> I have an error "function  st_x(geometry) does not exist" in an update
> statement which I don't understand. If I copy the same exact line in a
> select statement I get no error...
>
> Here is the update statement:
>
> update qwat_od.network_element set
> label_1_x = ST_X(ST_GeomFromEWKB(ST_Fineltra(ST_SetSRID(ST_
> MakePoint(label_1_x,label_1_y),21781), 'chenyx06.chenyx06_triangles',
> 'the_geom_lv03', 'the_geom_lv95')))
> where label_1_x is not null;
>
> and the select statement:
>
> select ST_X(ST_GeomFromEWKB(ST_Fineltra(ST_SetSRID(ST_
> MakePoint(label_1_x,label_1_y),21781), 'chenyx06.chenyx06_triangles',
> 'the_geom_lv03', 'the_geom_lv95')))
> from qwat_od.network_element
> where label_1_x is not null;
>
>
> How come that the select succees while not the update???
>

It looks like you are out of scope during the update execution, I mean, it
looks like the update is not able to identify the schema where PostGIS
functions and operators are defined. If this is the case, you could try to
fully-qualify the function during its execution

[...]
set label_1_x = <schema>.ST_X(<schema>.ST_GeomFromEWKB(<schema>.ST_
Fineltra(<schema>.ST_SetSRID(<schema>.ST_MakePoint(label_1_x,label_1_y),21781),
'chenyx06.chenyx06_triangles', 'the_geom_lv03', 'the_geom_lv95')))
[...]

where <schema> is the name of the schema where PostGIS functions are
defined - note, you probably need to fully-qualify the rest of the
functions.

You can check the schema through the psql command \df.

Hope this help,
Giuseppe.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20170718/d3cbf6ea/attachment.html>