Functions and anonymous types

From PostgreSQL wiki
Jump to navigationJump to search

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;