动态表查询选择

Question

大家好，我想创建一个在运行时更新的动态表查询。我有两个postgis表，一个包含点--tablename(记录)，另一个包含多边形--tablename(OpDMA)。此select查询：

Create table Op_DMAConn as
SELECT pol.id as polygon_id, poi.id as point_id 
FROM "OpDMA" pol
LEFT JOIN records poi ON (ST_Intersects(poi.geom, pol.geom))

返回点的polygon_id多边形和point_id。我希望这个查询在运行时执行！

Answer 1

我想你要找的是'EXECUTE‘语句。https://www.postgresql.org/docs/current/sql-execute.html

例如，您可以这样做：

CREATE OR REPLACE FUNCTION public.fn_pointer(points_table_name varchar, polygon_table_name varchar)
 returns  table(polygon_id int, point_id int)
LANGUAGE plpgsql AS
$$
declare
    final_query varchar;
begin
    query := 'SELECT ST_Contains(polygon.geom, point.geom)
    FROM public."'||points_table_name||'" point, public."'||polygon_table_name||'" polygon;'

    -- do what you have to do to make your query return you correct table

    return query execute final_query;
end
$$
;

select fn_pointer('table_name_1','table_name_2');

希望这能有所帮助。