procedure to check all the tables listed in GeometryColumn table for invalid geometries.

The procedure ask as parameter table for the results. If all tables has valid geometries the table will be empty otherwise it has 1 record for every table with invalid geometries. The record will have two column the query used and the number of invalid geometries found.

CREATE OR REPLACE FUNCTION massive_check_geometry_validity(tabris text)
RETURNS VOID AS $$
DECLARE
    sql text;
    sql2 text;
    _rec1 record;
    ris integer;
BEGIN
    sql := 'DROP TABLE IF EXISTS ' || quote_ident(tabris) ||';';
    execute(sql);
    sql := 'CREATE TABLE ' || quote_ident(tabris) || ' (id serial, query TEXT, invalidity INTEGER);';
    execute(sql);
    sql := 'select ''select count(*) from '' || quote_ident(f_table_schema) || ''.'' || quote_ident(f_table_name) || '' where ST_IsValid('' || f_geometry_column || '')=false;'' as query from public.geometry_columns order by f_table_schema,f_table_name LIMIT 3';

    FOR _rec1 IN execute (sql)
       LOOP
           raise notice 'executing: %', _rec1.query;
           execute(_rec1.query) into ris;
           sql := 'insert into ' || quote_ident(tabris) || ' (query, invalidity) values(' || quote_literal(_rec1.query) || ',' || ris || ');';
           execute(sql);
       END LOOP;
    RETURN;
END;
$$ LANGUAGE 'plpgsql' STRICT;

-- to use execute this query:
--     select massive_check_geometry_validity('result_table');
--

Happy checking, Andrea Peri.