Re: how to sort an input array before processing in pl/pgsql function

On Fri, Jul 23, 2010 at 11:36:14AM -0500, Kenneth Marshall wrote:
> Dear PostgreSQL community,
>
> I have the following function used to select tokens individually
> from a table and avoid a sequential scan of the table:
>
> create function lookup_tokens(integer,bigint[])
>   returns setof dspam_token_data
>   language plpgsql stable
>   as '
> declare
>   v_rec record;
> begin
>   for v_rec in select * from dspam_token_data
>     where uid=$1
>       and token in (select $2[i]
>         from generate_series(array_lower($2,1),array_upper($2,1)) s(i))
>   loop
>     return next v_rec;
>   end loop;
>   return;
> end;';
>
> I would like to execute the same lookup but with the array sorted. I
> used the following definition of sort:
>
> CREATE OR REPLACE FUNCTION sort(anyarray)
> RETURNS anyarray AS $$
>   SELECT array(SELECT * FROM unnest($1) ORDER BY 1);
> $$ language sql;
>
> and added a call to it to the function:
>
> create function lookup_tokens_sort(integer,bigint[])
>   returns setof dspam_token_data
>   language plpgsql stable
>   as '
> declare
>   v_rec record;
> begin
>   for v_rec in select * from dspam_token_data
>     where uid=$1
>       and token in (select (sort($2))[i]
>         from generate_series(array_lower($2,1),array_upper($2,1)) s(i))
>   loop
>     return next v_rec;
>   end loop;
>   return;
> end;';
>
> The problem with this is that the sort() appears to be called once
> per token because the run without the sort takes < 1 millisecond and
> with the sort take about 4 seconds. Is there a way to call the sort()
> only once and use the result in the loops instead of sorting each
> time. Any help would be appreciated. The actual sorted loop will
> be needed for the update functions to prevent deadlocks.
>
> Regards,
> Ken
>

As a follow-up, I can get the desired result by placing the
sort() call in the original SQL query:

SELECT * FROM lookup_tokens( xxx, sort(yyy));

Does anyone have any ideas on how to embed the sort()
call into the pl/pgsql function without a performance impact.

Cheers,
Ken