1

I have a fairly large spatial query that I wrote using the postgis extension. the query works very well but I would like to make into a function because for easier re-use

In concept: this query takes in a spatially enabled polygon parcels table and calculates acreage from various other polygon/point tables indicating specific land type features.

Currently if I would want this query to run on a different parcel polygon table. * I have tried using a left join lateral in an attempt to only have to insert the new parcels table once but the query took way way longer to run with that method

My question is how do I create a postgreSQL function where I can input a table as parameter then create a new table with a different name as the output.

I have found this post PostgreSQL: Pass table as argument in function

but I am having no luck at all

so something like this:

CREATE OR REPLACE FUNCTION hdc_report(out_name text,parcel_table regclass) 
RETURNS TABLE(pcl_mun text,pclblock character varying, pcllot character varying,contaminated_sites_tier1_points text,contaminated_tier2_points text,
        geol_mine_acres text,ecz_acres text,pre_act_const_no_slope_15_25_acres text,pre_act_sewer_service_acres text,
        pre_act_water_buffers_acres text,pre_act_flood_acres text,pre_act_slopes_15_25_acres text,pre_act_slopes_25_acres text,
        pre_act_streams_feet text,pre_act_waterbodies_acres text,total_acres text) AS 
$$
begin
return query execute
        'select b.pcl_mun,b.pclblock,b.pcllot,coalesce(q1.tier1,'0') as contaminated_sites_tier1_points,coalesce(q2.tier2,'0') as contaminated_tier2_points,
    coalesce(q3.geol_m,'0') as geol_mine_acres,coalesce(q4.lucz2,'0') as ecz_acres,coalesce(q5.slp15_25,'0') as pre_act_const_no_slope_15_25_acres,
    coalesce(q6.pre_sewer_service_area,'0') as pre_act_sewer_service_acres,coalesce(q7.water_buff,'0') as pre_act_water_buffers_acres,
    coalesce(q8.flood_prone,'0') as pre_act_flood_acres,COALESCE(q9.preact_slopes_15_25,'0') as pre_act_slopes_15_25_acres,
    COALESCE(q10.slopes25,'0') as pre_act_slopes_25_acres,COALESCE(q11.streams,'0') as pre_act_streams_feet,
    COALESCE(q12.wb,'0') as pre_act_waterbodies_acres,sum(st_area(b.shape))/43560 as total_acres
    from || parcel_table b 
        left join(select count(objectid_1)::text as tier1,pr.ssn,parcels.pcllot as pcllot,parcels.pclblock as pclblock 
                    from || parcel_table as parcels join ralph.contaminated_sites_tier1points as pr ON st_intersects(parcels.shape,pr.shape) 
                    group by parcels.pcllot,pr.ssn,pcllot,pclblock ) as q1
                on b.pcllot=q1.pcllot and b.pclblock=q1.pclblock
            left join(select count(objectid_1)::text as tier2,pr.ssn,parcels.pcllot as pcllot ,parcels.pclblock as pclblock 
                    from || parcel_table as parcels join ralph.contaminated_tier2points as pr ON st_intersects(parcels.shape,pr.shape) 
                    group by parcels.pcllot,pr.ssn,pcllot,pclblock ) as q2
                on b.pcllot=q2.pcllot and b.pclblock=q2.pclblock
            left join(select round(sum(st_area(st_intersection(pr.shape,parcels.shape)))::numeric,2) as geol_m,pr.mun,pr.county,parcels.pcllot as pcllot,parcels.pclblock as pclblock   
                    from || parcel_table as parcels join ralph.geol_mine pr ON st_intersects(parcels.shape,pr.shape)
                        group by parcels.pcllot,pr.mun,pr.county,pcllot,pclblock ) as q3
                ON b.pcllot = q3.pcllot and b.pclblock=q3.pclblock
            left join(select round(sum(st_area(st_intersection(pr.shape,parcels.shape))/43560)::numeric,2) as lucz2,pr.ssn,parcels.pcllot as pcllot,parcels.pclblock as pclblock 
                    from || parcel_table as parcels join ralph.lucz pr ON st_intersects(parcels.shape,pr.shape) where lucz_id = 'ECECSZ' or lucz_id = 'ECZ'
                        group by parcels.pcllot,pr.ssn,parcels.pcllot,pclblock ) as q4
                ON b.pcllot = q4.pcllot and b.pclblock=q4.pclblock
            left join(select round(sum(st_area(st_intersection(pr.shape,parcels.shape))/43560)::numeric,2) as slp15_25,parcels.pcllot as pcllot,parcels.pclblock as pclblock 
                    from || parcel_table as parcels join ralph.pre_act_const_no_slp15_25 as pr on parcels.shape && pr.shape
                    group by parcels.pcllot,parcels.pcl_mun,pclblock ) as q5
                ON b.pcllot = q5.pcllot and b.pclblock=q5.pclblock
            left join(select round(sum(st_area(st_intersection(pr.shape,parcels.shape))/43560)::numeric,2) as pre_sewer_service_area,parcels.pcllot as pcllot,parcels.pclblock as pclblock 
                    from || parcel_table as parcels join ralph.pre_act_sewer_service_area as pr on parcels.shape && pr.shape
                    group by parcels.pcllot,parcels.pcl_mun,pclblock ) as q6
                ON b.pcllot = q6.pcllot and b.pclblock=q6.pclblock
            left join(select round(sum(st_area(st_intersection(pr.shape,parcels.shape))/43560)::numeric,2) as water_buff,parcels.pcllot as pcllot,parcels.pclblock as pclblock 
                    from || parcel_table as parcels join ralph.pre_act_water_buffers as pr on parcels.shape && pr.shape
                    group by parcels.pcllot,parcels.pcl_mun,pclblock ) as q7
                ON b.pcllot = q7.pcllot and b.pclblock=q7.pclblock
            left join(select round(sum(st_area(st_intersection(pr.shape,parcels.shape))/43560)::numeric,2) as flood_prone,parcels.pcllot as pcllot,parcels.pclblock as pclblock 
                    from || parcel_table as parcels join ralph.preact_flood_prone_flood_hazard as pr on parcels.shape && pr.shape
                    group by parcels.pcllot,parcels.pcl_mun,pclblock ) as q8
                ON b.pcllot = q8.pcllot and b.pclblock=q8.pclblock
            left join(select round(sum(st_area(st_intersection(pr.shape,parcels.shape))/43560)::numeric,2) as preact_slopes_15_25,parcels.pcllot as pcllot,parcels.pclblock as pclblock 
                    from || parcel_table as parcels join ralph.preact_slopes_15_25_percent as pr on parcels.shape && pr.shape
                    group by parcels.pcllot,parcels.pcl_mun,pclblock ) as q9
                ON b.pcllot = q9.pcllot and b.pclblock=q9.pclblock
            left join(select round(sum(st_area(st_intersection(pr.shape,parcels.shape))/43560)::numeric,2) as slopes25,parcels.pcllot as pcllot,parcels.pclblock as pclblock 
                    from || parcel_table as parcels join ralph.preact_slopes_25_percent as pr on parcels.shape && pr.shape
                    group by parcels.pcllot,parcels.pcl_mun,pclblock ) as q10
                ON b.pcllot = q10.pcllot and b.pclblock=q10.pclblock
            left join(select round(sum(st_length(st_intersection(pr.shape,parcels.shape)))::numeric,2) as streams,parcels.pcllot as pcllot,parcels.pclblock as pclblock 
                    from || parcel_table as parcels join ralph.preact_streams as pr on parcels.shape && pr.shape
                    group by parcels.pcllot,parcels.pcl_mun,pclblock ) as q11
                ON b.pcllot = q11.pcllot and b.pclblock=q11.pclblock
            left join(select round(sum(st_area(st_intersection(pr.shape,parcels.shape))/43560)::numeric,2) as wb,parcels.pcllot as pcllot,parcels.pclblock as pclblock 
                    from || parcel_table as parcels join ralph.preact_water_bodies as pr on parcels.shape && pr.shape
                    group by parcels.pcllot,parcels.pcl_mun,pclblock ) as q12
                ON b.pcllot = q12.pcllot and b.pclblock=q12.pclblock
    group by b.pcl_mun,b.pclblock,b.pcllot,contaminated_sites_tier1_points,contaminated_tier2_points,geol_mine_acres,ecz_acres,
    pre_act_const_no_slope_15_25_acres,pre_act_sewer_service_acres,pre_act_water_buffers_acres,pre_act_flood_acres,pre_act_slopes_15_25_acres,
    pre_act_slopes_25_acres,pre_act_streams_feet,pre_act_waterbodies_acres;'
end

$$ LANGUAGE plpgsql;
  • 1
    The big string-as-a-query is syntactically invalid. First, check out the format() function with %I placeholders to inject easily your table names into your dynamic SQL. – Daniel Vérité May 6 '17 at 16:53
  • 1
    The second syntax problem is unquoted single quotes inside quotes. To solve that, use a dollar-quoted string for the entire query, as in $query$ SELECT all-kind-of-stuff $query$ – Daniel Vérité May 6 '17 at 16:57
  • mmm im just going to write the function in python, plpsql does not seem so user friendly – ziggy May 8 '17 at 16:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.