Блог

Я пытаюсь выполнить динамический запрос. У меня есть статический запрос, который работает :

 CREATE OR REPLACE
FUNCTION update_points_in_polygon(
)
  RETURNS trigger LANGUAGE plpgsql AS $function$
  -- Rule for clarity : one filter per function. Can choose three options : point count unit count and label concat
  -- the one rule will be applied to all 3 options if they are selected
DECLARE tmprow record;
  BEGIN   
FOR tmprow IN 
SELECT c.objectid, count(a.*) point_count 
    FROM sandbox.simple_address a
    JOIN sandbox.polygon_address_units c
    ON st_intersects(a.wkb_geometry, c.wkb_geometry)            
WHERE st_intersects(c.wkb_geometry, NEW.wkb_geometry)
    GROUP BY c.objectid -- tmp TABLE fetchin nb OF addresses IN a polygon WHERE polygon interects point
LOOP
    UPDATE sandbox.polygon_address_units
        SET address_count = tmprow.point_count
        WHERE objectid = tmprow.objectid; -- UPDATE point_count COLUMN OF TABLES fetched IN FIRST SELECT 
END LOOP;
RETURN NEW;
END;
$function$;
 

Когда я пытаюсь заменить петлю вот так :

 EXECUTE 'FOR tmprow IN 
SELECT c.objectid, count(a.*) point_count 
    FROM sandbox.simple_address a
    JOIN sandbox.polygon_address_units c
    ON st_intersects(a.wkb_geometry, c.wkb_geometry)            
WHERE st_intersects(c.wkb_geometry, NEW.wkb_geometry)
    GROUP BY c.objectid 
LOOP
    UPDATE sandbox.polygon_address_units
        SET address_count = tmprow.point_count
        WHERE objectid = tmprow.objectid;
END LOOP;';
 

Я получаю следующее сообщение :

Ошибка SQL [42601]: ОШИБКА: синтаксическая ошибка в или рядом с «ДЛЯ», где: функция PL/pgSQL update_points_in_polygon() строка 7 при ВЫПОЛНЕНИИ

Цель состоит в том, чтобы иметь возможность динамически определять имена таблиц и столбцов. Я также попытался сделать два оператора execute и сохранить ЦИКЛ в plpgsql вместо обычного SQL, который запускается с помощью EXECUTE:

 CREATE OR REPLACE
FUNCTION update_points_in_polygon(
)
  RETURNS trigger LANGUAGE plpgsql AS $function$
  -- Rule for clarity : one filter per function. Can choose three options : point count unit count and label concat
  -- the one rule will be applied to all 3 options if they are selected
DECLARE tables_schema varchar;
DECLARE polygon_table_name varchar;
DECLARE polygon_point_count_column_name varchar;
DECLARE point_table_name varchar;

DECLARE tmprow record;

BEGIN
tables_schema = TG_ARGV[0]; -- frontier_ftth
polygon_table_name = TG_ARGV[1]; -- fdh
polygon_point_count_column_name = TG_ARGV[2]; -- point_count
point_table_name = TG_ARGV[4];

FOR tmprow IN 
        EXECUTE format('SELECT c.objectid, count(a.*) point_count 
            FROM %I.%I a
            JOIN %I.%I c
            ON st_intersects(a.wkb_geometry, c.wkb_geometry)            
        WHERE st_intersects(c.wkb_geometry, st_geomFromText(''%s'', 4326))
            GROUP BY c.objectid',
            tables_schema, point_table_name, tables_schema, polygon_table_name, 
            st_astext(st_geomfromewkb(NEW.wkb_geometry)))
        LOOP
            EXECUTE format('UPDATE %I.%I c1
                SET %I = tmprow.point_count
                WHERE c1.objectid = tmprow.objectid', 
                tables_schema, polygon_table_name, polygon_point_count_column_name);
            END LOOP;
RETURN NEW;
END;
$function$
 

With this version of the function, I get the following error :

SQL Error [22004]: ERROR: null values cannot be formatted as an SQL identifier
Where: PL/pgSQL function update_points_in_polygon() line 17 at FOR over EXECUTE statement

This is a barebones version of my setup :

 CREATE TABLE sandbox.simple_address (
    objectid serial4 NOT NULL,
    wkb_geometry geometry(point, 4326) NULL,
);

CREATE TABLE sandbox.polygon_address_units (
    objectid serial4 NOT NULL,
    address_count int4 NULL,
    wkb_geometry geometry(multipolygon, 4326) NULL,
);

CREATE TRIGGER onallactions AFTER INSERT
OR UPDATE ON
sandbox.simple_address FOR EACH ROW
WHEN ((pg_trigger_depth() < 1)) EXECUTE PROCEDURE 
update_points_in_polygon(
'sandbox', 'polygon_address_units', 'address_count', 
'simple_address');

INSERT INTO sandbox.polygon_address_units(wkb_geometry) 
VALUES(ST_SetSRID(st_astext(st_geomfromtext('MULTIPOLYGON (((-1 1, 1 1, 1 -1, -1 -1, -1 1)))')),4326));

INSERT INTO sandbox.simple_address(wkb_geometry) 
VALUES(ST_SetSRID(st_astext(st_geomfromtext('POINT(0 0)')),4326));
 

How do use these two queries to properly update the number_of_points column in my polygon layers when I add a point that intersects it ? There might be multiple polygons to update.

EDIT: The solution was to call tmprow to format the second string

 FOR tmprow IN 
        EXECUTE format('SELECT c.objectid, count(a.*) point_count 
            FROM %I.%I a
            JOIN %I.%I c
            ON st_intersects(a.wkb_geometry, c.wkb_geometry)            
        WHERE st_intersects(c.wkb_geometry, st_geomFromText(''%s'', 4326))
            GROUP BY c.objectid',
            tables_schema, point_table_name, tables_schema, polygon_table_name, 
            st_astext(st_geomfromewkb(NEW.wkb_geometry)))
        LOOP
            EXECUTE format('UPDATE %I.%I c1
                SET %I = %s
                WHERE c1.objectid = %s ', 
                tables_schema, polygon_table_name, polygon_point_count_column_name, tmprow.point_count, tmprow.objectid);
            END LOOP;
RETURN NEW;
END;
$function$