函数和匿名类型

片段

聚合范围

适用于 PostgreSQL

任何版本

用

SQL

依赖于

无

可以定义一个返回 RECORD 的函数，包含多个列，形成一个匿名类型 1

使用参数调用函数，并以命名列的形式检索结果，需要特定的语法... 以下是一个有效的示例 (pg9) 和解决方法。(注意 - PostGIS 几何与示例无关...)

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;