Re: Functions returning setof record -- can I use a table type as my return type hint?

> On 12/08/2011 17:26, George MacKerron wrote:
>>
>> The point of the function is that you can pass it any table name
>> (along with some other parameters) and it returns rows from that
>> named table.
>
> OK, fair enough.... but what exactly are you trying to do that a simple
> SELECT won't do?
>
> Ray.
>
> --
> Raymond O'Donnell :: Galway :: Ireland
> rod(at)iol(dot)ie

I was writing myself an easy-to-use generic nearest-neighbour function for PostGIS that returns whole rows from the target table, using expanding search radii to avoid calculating distances for every geometry in the table. Of course, this will hopefully become redundant in the near future with the introduction of k-nearest-neighbour GiST indices in PG 9.1.

The function is as follows (I'm no PL/pgSQL guru -- comments welcome):

create or replace function
nnrecords(
nearTo geometry
, initialDistance real
, distanceMultiplier real
, maxPower integer
, nearThings text
, nearThingsGeometryField text
, numWanted integer)
returns setof record as $$
declare
i integer;
sql text;
enough boolean;
begin
i := 0;
while i <= maxPower loop
sql := ' select count(1) >= $5 from ' || quote_ident(nearThings)
|| ' where st_dwithin($1, ' || quote_ident(nearThingsGeometryField) || ', $2 * ($3 ^ $4))';
execute sql into enough using
nearTo -- $1
, initialDistance -- $2
, distanceMultiplier -- $3
, i -- $4
, numWanted; -- $5
if enough or i = maxPower then
sql := ' select * from ' || quote_ident(nearThings)
|| ' where st_expand($1, $2 * ($3 ^ $4)) && ' || quote_ident(nearThingsGeometryField)
|| ' order by st_distance($1, ' || quote_ident(nearThingsGeometryField) || ')'
|| ' limit $5';
return query execute sql using
nearTo -- $1
, initialDistance -- $2
, distanceMultiplier -- $3
, i -- $4
, numWanted; -- $5
return;
end if;
i := i + 1;
end loop;
end
$$ language 'plpgsql' stable;

Please access the attached hyperlink for an important electronic communications disclaimer: http://lse.ac.uk/emailDisclaimer