Re: Return 30% of results?

On Sat, Sep 19, 2009 at 3:27 AM, nick <nboutelier(at)gmail(dot)com> wrote:
> On Sep 18, 3:40 pm, spam_ea(dot)(dot)(dot)(at)gmx(dot)net (Thomas Kellerer) wrote:
>> Nick wrote on 18.09.2009 23:31:> Is there a way to return a percentage of the rows found? I tried
>> > window functions but get an error...
>>
>> > ERROR:  syntax error at or near "OVER"
>>
>> > SELECT id, cume_dist FROM (
>> >   SELECT id, cume_dist() OVER (ORDER BY id) FROM employees
>> > ) s
>> > WHERE cume_dist < 0.3
>>
>> Works for me. Are you sure you are using Postgrs 8.4?
>> (Windowing functions are not available in earlier versions)

Using 8.3? You can use arrays to stack a set and then pull some records out:

create or replace function some_employees() returns setof employee as
$$
declare
employees_arr employees[];
n_found int;
n_returned int;
begin
select array
(
select employees from employees
where x order by y
) into employees_arr;

n_found := array_upper(employees_arr, 1);
n_returned := n_found * 0.3;
return query select (e).* from unnest(employees_arr[1:n_returned]) as e;
end;
$$ language plpgsql;

couple notes:
*) 8.3 has doesn't have unnest, but it can be made (google array_explode).
*) probably some typos in above...i didn't test it
*) needs some error checking
*) not scalable to huge numbers of records
*) 8.4 can do this much easier!

merlin