CODEWITHSUNDEEP
sqlpostgresqlfunctionpostgis
Jepessen
Jepessen

Reputation: 12415

Store result of a query inside a function

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?

Upvotes: 0

Views: 38

Answers (1)

Glenn
Glenn

Reputation: 9150

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
;

Upvotes: 1

Related Questions