dynamic table query select

0
votes

Hello guys I want to create a dynamic table query to be updated on runtime. I have two postgis tables one that contains points--tablename(records) and one that contains polygons--tablename(OpDMA). This select query:

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))

returns the polygon_id polygons and point_id of the points. I would like this query to be executed on runtime!

1
postgresqlpostgis

1 Answers

1
votes

I think what you are looking for is the 'EXECUTE' statement. https://www.postgresql.org/docs/current/sql-execute.html

For example you can do that :

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');

Hope this helps.