# Functions and anonymous types

From PostgreSQL wiki

Jump to navigationJump to searchIt 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;
```