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

Поиск
Список
Период
Сортировка
От Kenneth Marshall
Тема how to sort an input array before processing in pl/pgsql function
Дата
Msg-id 20100723163614.GA17529@aart.is.rice.edu
обсуждение исходный текст
Ответы Re: how to sort an input array before processing in pl/pgsql function  (Kenneth Marshall <ktm@rice.edu>)
Список pgsql-novice
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

В списке pgsql-novice по дате отправления:

Предыдущее
От: viju
Дата:
Сообщение: could not change directory to "/root"
Следующее
От: Kenneth Marshall
Дата:
Сообщение: Re: how to sort an input array before processing in pl/pgsql function