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
|
Список | 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 по дате отправления: