Обсуждение: 'within group'- or percentile_cont-expression seems to have ramifications on table ordering

create table test (
    id integer not null primary key,
    value integer not null,
    value_percentile integer not null default 0
);

insert random values into the 'value' column

do $$
    begin
        for r in 0..100 loop
                insert into test(id, value) values (r, random() * 100);
            end loop;
    end;
$$;

compute the percentiles for those values and write them to the percentile-column

do $$
    declare
        _value integer;
    begin
        for r in 0..100 loop
                select  into _value percentile_cont(r::float / 100) within group (order by test.value) from test;
                raise notice '%: %',r::float / 100,  _value;

                update test set value_percentile = r where value = _value;
            end loop;
    end
$$;

inspect the table

select * from test;

you can see that the rows are not in order of insertion any more, but in descending order of value. That may not violate the specification, however I find it 

to be counterintuitive that a non-updating query would have such side effects.

best

Bernd