Functions and anonymous types
From PostgreSQL wiki
It is possible to define a function that returns RECORD, consisting of multiple columns, which forms an Anonymous Type 1
Calling the function with parameters, and retrieving the results as named columns, needs particular syntax... Here is a working example (pg9) and the work-arounds. (note - the PostGIS geometry is ancillary to the example..)
CREATE OR REPLACE FUNCTION get_buffered_avals_out( in_id150 int, in_dist double precision, in_geom geometry, OUT id150 int, OUT distance double precision, OUT wkb_geometry geometry, OUT a_res_sum float, OUT a_emp_sum float, OUT du_sum float, OUT pop_sum float, OUT emp_sum float, OUT emp_retail_sum float, -- 20 more parameters here -- OUT pop_age65_up_sum float ) AS $$ SELECT $1 AS id150, $2 AS distance, $3 AS wkb_geometry, sum(r.a_res) AS a_res_sum, sum(r.a_emp) AS a_emp_sum, sum(r.du) AS du_sum, sum(r.pop) AS pop_sum, sum(r.emp) AS emp_sum, sum(r.emp_retail) AS emp_retail_sum, -- 20 more sums here -- sum(r.pop_age65_up) FROM s_base r WHERE st_dwithin( $3, r.wkb_geometry, $2); $$ COST 10000 LANGUAGE SQL STABLE strict; ----------------------------------------------------------- -- create a table of input values for convenience -------- CREATE TABLE tmp_res22 AS SELECT s.id150, t.prod_hbw * 1609.0 AS msearch_radius, s.wkb_geometry FROM s_subset s INNER JOIN sg_grid150m g ON s.id150 = g.id150 INNER JOIN sgt_base t ON g.tay_id = t.tay_id ; ----------------------------------------------------------- -- A trivial execution of the function using pre-stored params SELECT get_buffered_avals_out(id150, msearch_radius, wkb_geometry) FROM tmp_res22; ---------------------------------------------------------- -- in order to get params to the function, and retrieve them by name, this syntax works SELECT (f).* FROM (SELECT get_buffered_avals_out(id150, msearch_radius,wkb_geometry) AS f FROM tmp_res22 ) s; -- in Pg9, OFFSET 0 is required to prevent the query plan from including unneeded calls to the function SELECT (f).* FROM (SELECT get_buffered_avals_out(id150, msearch_radius,wkb_geometry) AS f FROM tmp_res22 offset 0) s;
