Function or loop using table and topologies names as arguments in Postgresql

Question

I'm working with topologies in PostGIS and to create a TopoGeometry column, I'm using this loop:

DO $$DECLARE r record;
BEGIN
 FOR r IN SELECT * FROM table_uf_11 LOOP
  BEGIN
    UPDATE table_uf_11 SET tg_geom = toTopoGeom(ST_Force2D(geom),'topology_uf_11', 1, 1) 
    WHERE gid= r.gid;
   EXCEPTION
    WHEN OTHERS THEN
     RAISE WARNING 'Loading of record % failed: %', r.gid, SQLERRM;
  END;
 END LOOP;
END$$;

The reason for using this loop is because in some rows the toTopoGeom function displays error, but are just a few cases, for exemplo 38 cases in 24.000. Using this structure I can identify which cases are problematic in the log and fix them later.

My problem is that I have another 26 tables with their respective topologies, all of them identified by the state code, for exemplo:

table_uf_12 / topology_uf_12
table_uf_13 / topology_uf_13
table_uf_14 / topology_uf_14
...
table_uf_53 / topology_uf_53

The state code are not necessarily sequential, but the names has the same pattern. Column names as geom and tg_geom are equal for all tables.

How can I make a function or another loop structure to replicate this process in all 27 tables and the same time save the log of each table? I tried to make a function, but in this case the arguments would be the table name and the topology name, and i'm having difficult to elaborate this structure.

Any suggestions?

score 1 · Accepted Answer · answered Jan 15 '16 at 20:59

1

I think this should do it:

DO $BODY$
DECLARE
  t regclass;
  gid bigint;
BEGIN
  FOR t IN SELECT oid::regclass FROM pg_class WHERE relname ~ '^table_uf_\d+$' LOOP
    FOR gid IN EXECUTE 'SELECT gid FROM ' || t::text LOOP
      BEGIN
        EXECUTE
          ' UPDATE ' || t::text ||
          ' SET tg_geom = toTopoGeom(ST_Force2D(geom), $2, 1, 1)'
          ' WHERE gid = $1'
        USING gid, replace(t::text, 'table', 'topology');
      EXCEPTION
        WHEN OTHERS THEN
          RAISE WARNING 'Loading of record % failed: %', gid, SQLERRM;
      END;
    END LOOP;
  END LOOP;
END
$BODY$

answered Jan 15 '16 at 20:59

Nick Barnes

19,816
3
51
63

Nick, this loop is getting this message for all records: "WARNING: Loading of record 609 failed: function totopogeom(geometry, text, integer, integer) does no exist". – Roberto Jan 18 '16 at 12:54
@Roberto: You might need to schema-qualify the function, i.e. `topology.toTopoGeom(...)` – Nick Barnes Jan 19 '16 at 00:12

Function or loop using table and topologies names as arguments in Postgresql

1 Answers1