I've the following function:
DO
$do$
DECLARE
maxgid integer;
tableloop integer;
obstacle geometry;
simplifyedobstacle geometry;
BEGIN
select max(gid) from public.terrain_obstacle_temp into maxgid;
FOR tableloop IN 1 .. maxgid
LOOP
insert into public.terrain_obstacle (tse_coll,tse_height,geom) select tse_coll,tse_height,geom
from public.terrain_obstacle_temp where gid = tableloop;
END LOOP;
END
$do$;
I need to modify this function in order to execute different queries according to the type of a column of public.terrain_obstacle_temp.
This is a temporary table created by reading a shapefile, and I need to know the kind of the geom column of that table. I have a query that give the data to me:
SELECT type
FROM geometry_columns
WHERE f_table_schema = 'public'
AND f_table_name = 'terrain_obstacle'
and f_geometry_column = 'geom';
It returns me a character_varying value (in this case MULTIPOLYGON).
Ho can I modify the function in order to get the result of the query, and create an if statement that allows me to execute some code according to the result of that query?
Is the intention to copy all the records from the temp table to the actual table? If so, you may be able to skip the loop:
insert into public.terrain_obstacle (tse_coll, tse_height, geom)
select tse_coll, tse_height, geom
from public.terrain_obstacle_temp
;
Do terrain_obstacle and terrain_obstacle_temp have the same structure? If so, then the "insert into ... select ..." should work fine provided the column types are the same.
If conditional typing is required, use the CASE WHEN syntax:
v_type geometry_columns.type%TYPE;
...
SELECT type
INTO v_type
FROM geometry_columns
WHERE f_table_schema = 'public'
AND f_table_name = 'terrain_obstacle'
AND f_geometry_column = 'geom'
;
insert into public.terrain_obstacle (tse_coll, tse_height, geom)
select tse_coll
,tse_height
,CASE WHEN v_type = 'MULTIPOLYGON' THEN my_func1(geom)
WHEN v_type = 'POINT' THEN my_func2(geom)
ELSE my_default(geom)
END
from public.terrain_obstacle_temp
;
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments