Functions and anonymous types

From PostgreSQL wiki

Revision as of 22:42, 19 May 2012 by Boshomi (Talk | contribs)

(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to: navigation, 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;
Personal tools