Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



> 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@xxxxxx

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

-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux