2019年9月10日星期二

PostgreSQL: Dynamic SQL Function

PostgreSQL: Dynamic SQL Function

CREATE TEMP TABLE "千河套__西防洪堤以西2" As  SELECT  sjx.*    from xqh_v_sjx as sjx , xqh_v_zones as v_zone   where ST_Within(sjx.geom,v_zone.geom)  AND v_zone.zone_name =  '千河套__西防洪堤以西' ;


DO 
$$
DECLARE
zone_name1 text;
BEGIN
FOR zone_name1 IN SELECT zones.zone_name 
FROM xqh_v_zones as zones 
WHERE zones.zone_name <> '待删除区域' 
LOOP
--RAISE NOTICE '%', zone_name1;
EXECUTE  format('CREATE TEMP TABLE %s AS SELECT  sjx.*   from xqh_v_sjx as sjx , xqh_v_zones as v_zone  where ST_Within(sjx.geom,v_zone.geom)  AND v_zone.zone_name =  %L',quote_ident(zone_name1),zone_name1);
--EXECUTE 'CREATE TEMP TABLE "'  || zone_name1  || '" As '  || ' SELECT  *   ' || ' from xqh_v_sjx as sjx , xqh_v_zones as v_zone  ' || ' where ST_Within(sjx.geom,v_zone.geom)  AND v_zone.zone_name =  ' || zone_name1 || ' ;' ;
END LOOP;
END;
$$;

没有评论: