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!
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.
