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@iol.ie

I was writing myself an easy-to-use generic nearest-neighbour function for PostGIS that returns whole rows from the
targettable, using expanding search radii to avoid calculating distances for every geometry in the table. Of course,
thiswill 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