The following bug has been logged on the website:
Bug reference: 15990
Logged by: Andreas Wicht
Email address: a.wicht@gmail.com
PostgreSQL version: 11.5
Operating system: Ubuntu 18.04
Description:
Hi there,
I am not sure where to place this problem, here or at the PostGIS mailing
list. I'd like to start here though.
I have a function which needs a commit after each loop (inserting a result
into a target table). So far I worked around this requirement with dblink.
When the new procedures were implemented I tried to port the function to a
procedure, greatly reducing the complexity.
While testing I started to get the above mentioned error.
I could dumb the procedure down to the very basics to reproduce the error.
Note that the procedure fails as soon as the geometry column is part of the
SELECT statement defining the FOR loop.
Researching this error did not yield any useful information to me (at least
none which is evident to me).
Steps to reproduce:
CREATE EXTENSION postgis;
CREATE SCHEMA temp;
wget
https://www.statistik-berlin-brandenburg.de/opendata/RBS_OD_ORT_2016_12.zip
unzip RBS_OD_ORT_2016_12.zip
shp2pgsql -I -g geom -s 25833 RBS_OD_ORT_2016_12.shp temp.test | psql -h XXX
-p XXX -d XXX -U XXX
CREATE TABLE temp.mytable (gid integer, geom geometry);
CREATE OR REPLACE PROCEDURE temp.testprocedure(polygon_tbl regclass)
AS $$
DECLARE
_poly_tbl ALIAS FOR $1;
_rcd RECORD;
BEGIN
FOR _rcd IN
EXECUTE format ('SELECT gid, geom FROM %s', _poly_tbl)
LOOP
INSERT INTO temp.mytable (gid, geom) VALUES (_rcd.gid, _rcd.geom);
COMMIT;
END LOOP;
END;
$$
LANGUAGE plpgsql;
CALL temp.testprocedure('temp.test');
---------
PostGIS version:
POSTGIS="2.5.2 r17328" [EXTENSION] PGSQL="110" GEOS="3.6.2-CAPI-1.10.2
4d2925d6" PROJ="Rel. 4.9.3, 15 August 2016" GDAL="GDAL 2.2.3, released
2017/11/20" LIBXML="2.9.4" LIBJSON="0.12.1" LIBPROTOBUF="1.2.1" TOPOLOGY
RASTER
PostgeSQL version:
PostgreSQL 11.5 (Ubuntu 11.5-1.pgdg18.04+1) on x86_64-pc-linux-gnu, compiled
by gcc (Ubuntu 7.4.0-1ubuntu1~18.04.1) 7.4.0, 64-bit
Greetings
Andreas
