Functions and anonymous types

Snippets

Aggregate Range

Works with PostgreSQL

Any version

Written in

SQL

Depends on

Nothing

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;