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

Hello

2011/8/13 George MacKerron <g(dot)j(dot)mackerron(at)lse(dot)ac(dot)uk>:
>> 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;
>

* move non necessary rows from cycle.
* use a statement FOR instead WHILE
* flag STABLE is wrong, your function is VOLATILE

Regards

Pavel Stehule

>
> Please access the attached hyperlink for an important electronic communications disclaimer: http://lse.ac.uk/emailDisclaimer
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>